|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Greetings everyone. I use the following to convert my dates to text format:
CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with tbljob_info.SplECD
being smalldatetime. Basically it just lops off the time. It works fine,
but my problem is this. For one of the dates in my query I enter
'dbo.tbljob_info.SplECD-7' to show this date minus one week. It also works
fine, but I can not figure out the syntax to combine the CONVERT expression
with the -7. Thanks for any help.
|
|
Greg,
Try using Cast instead of Convert. Cast does the conversion without losing
scale.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com...
[Quoted Text] > Greetings everyone. I use the following to convert my dates to text
> format:
> CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with
> tbljob_info.SplECD
> being smalldatetime. Basically it just lops off the time. It works fine,
> but my problem is this. For one of the dates in my query I enter
> 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It also
> works
> fine, but I can not figure out the syntax to combine the CONVERT
> expression
> with the -7. Thanks for any help.
>
|
|
Sorry, but I'm unable to reproduce your problem. Could you be more explicit
about your exact problem?
Are-you trying to subtract -7 *after* the conversion to nvarchar(10)?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com...
[Quoted Text] > Greetings everyone. I use the following to convert my dates to text
> format:
> CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with
> tbljob_info.SplECD
> being smalldatetime. Basically it just lops off the time. It works fine,
> but my problem is this. For one of the dates in my query I enter
> 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It also
> works
> fine, but I can not figure out the syntax to combine the CONVERT
> expression
> with the -7. Thanks for any help.
>
|
|
Every night a dts package executes to send a text file to another server. My
problem is the dates show up on the text file like this '01/01/2006 12:00'.
The administrator of the other server tells me I need to lop of the time and
send only the date. When I run the CONVERT command below it returns only
'01/01/2006' without the time. One of my dates is generated by an expression
where I simply add a minus sign and a number, hence the -7, after whatever
datefield I wish to show a date 7 days less. Here is an exact example:
SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, SplECD - 7 AS
Expr2
FROM dbo.tbljob_info
This works fine until I try to convert Expr2. My problem is that I can not
figure out how to get the -7 *and* the CONVERT to work.
"Sylvain Lafontaine" wrote:
[Quoted Text] > Sorry, but I'm unable to reproduce your problem. Could you be more explicit > about your exact problem? > > Are-you trying to subtract -7 *after* the conversion to nvarchar(10)? > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... > > Greetings everyone. I use the following to convert my dates to text > > format: > > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with > > tbljob_info.SplECD > > being smalldatetime. Basically it just lops off the time. It works fine, > > but my problem is this. For one of the dates in my query I enter > > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It also > > works > > fine, but I can not figure out the syntax to combine the CONVERT > > expression > > with the -7. Thanks for any help. > > > > > |
|
Graham, when I try this I get the error message 'Poorly formed cast function'
"Graham R Seach" wrote:
[Quoted Text] > Greg,
>
> Try using Cast instead of Convert. Cast does the conversion without losing
> scale.
>
> Regards,
> Graham R Seach
> Microsoft Access MVP
> Sydney, Australia
> ---------------------------
>
> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com...
> > Greetings everyone. I use the following to convert my dates to text
> > format:
> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with
> > tbljob_info.SplECD
> > being smalldatetime. Basically it just lops off the time. It works fine,
> > but my problem is this. For one of the dates in my query I enter
> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It also
> > works
> > fine, but I can not figure out the syntax to combine the CONVERT
> > expression
> > with the -7. Thanks for any help.
> >
>
>
>
|
|
Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com...
[Quoted Text] > Every night a dts package executes to send a text file to another server. > My > problem is the dates show up on the text file like this '01/01/2006 > 12:00'. > The administrator of the other server tells me I need to lop of the time > and > send only the date. When I run the CONVERT command below it returns only > '01/01/2006' without the time. One of my dates is generated by an > expression > where I simply add a minus sign and a number, hence the -7, after whatever > datefield I wish to show a date 7 days less. Here is an exact example: > > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, SplECD - 7 AS > Expr2 > FROM dbo.tbljob_info > > This works fine until I try to convert Expr2. My problem is that I can not > figure out how to get the -7 *and* the CONVERT to work. > > > "Sylvain Lafontaine" wrote: > >> Sorry, but I'm unable to reproduce your problem. Could you be more >> explicit >> about your exact problem? >> >> Are-you trying to subtract -7 *after* the conversion to nvarchar(10)? >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... >> > Greetings everyone. I use the following to convert my dates to text >> > format: >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with >> > tbljob_info.SplECD >> > being smalldatetime. Basically it just lops off the time. It works >> > fine, >> > but my problem is this. For one of the dates in my query I enter >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It also >> > works >> > fine, but I can not figure out the syntax to combine the CONVERT >> > expression >> > with the -7. Thanks for any help. >> > >> >> >>
|
|
Yes. This is what I have tried, and the problems with them:
CONVERT (nvarchar(10), SplECD - 7, 101)
Error 'Data type error in expression'
CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101)
Error same as above
CONVERT (nvarchar(10), SplECD, 101)-7
No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', and
returns only the expression text.
CONVERT((nvarchar(10), SplECD, 101) - 7)
Error 'Invalid or missing expression'
It seems like it must be something so simple.
"Sylvain Lafontaine" wrote:
[Quoted Text] > Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... > > Every night a dts package executes to send a text file to another server. > > My > > problem is the dates show up on the text file like this '01/01/2006 > > 12:00'. > > The administrator of the other server tells me I need to lop of the time > > and > > send only the date. When I run the CONVERT command below it returns only > > '01/01/2006' without the time. One of my dates is generated by an > > expression > > where I simply add a minus sign and a number, hence the -7, after whatever > > datefield I wish to show a date 7 days less. Here is an exact example: > > > > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, SplECD - 7 AS > > Expr2 > > FROM dbo.tbljob_info > > > > This works fine until I try to convert Expr2. My problem is that I can not > > figure out how to get the -7 *and* the CONVERT to work. > > > > > > "Sylvain Lafontaine" wrote: > > > >> Sorry, but I'm unable to reproduce your problem. Could you be more > >> explicit > >> about your exact problem? > >> > >> Are-you trying to subtract -7 *after* the conversion to nvarchar(10)? > >> > >> -- > >> Sylvain Lafontaine, ing. > >> MVP - Technologies Virtual-PC > >> E-mail: http://cerbermail.com/?QugbLEWINF> >> > >> > >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... > >> > Greetings everyone. I use the following to convert my dates to text > >> > format: > >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with > >> > tbljob_info.SplECD > >> > being smalldatetime. Basically it just lops off the time. It works > >> > fine, > >> > but my problem is this. For one of the dates in my query I enter > >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It also > >> > works > >> > fine, but I can not figure out the syntax to combine the CONVERT > >> > expression > >> > with the -7. Thanks for any help. > >> > > >> > >> > >> > > > |
|
Are you sure that SplECD is a datetime or a smalldatetime? I've just created
a table with a smalldatetime field and no problem using something like
CONVERT (nvarchar(10), SplECD - 7, 101).
Is SplECD the name of the field in the table or an alias?
How and where are you using this expression exactly? In a Select statement
or somewhere else?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com...
[Quoted Text] > Yes. This is what I have tried, and the problems with them: > CONVERT (nvarchar(10), SplECD - 7, 101) > Error 'Data type error in expression' > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) > Error same as above > CONVERT (nvarchar(10), SplECD, 101)-7 > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', and > returns only the expression text. > CONVERT((nvarchar(10), SplECD, 101) - 7) > Error 'Invalid or missing expression' > > It seems like it must be something so simple. > > > > > > "Sylvain Lafontaine" wrote: > >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... >> > Every night a dts package executes to send a text file to another >> > server. >> > My >> > problem is the dates show up on the text file like this '01/01/2006 >> > 12:00'. >> > The administrator of the other server tells me I need to lop of the >> > time >> > and >> > send only the date. When I run the CONVERT command below it returns >> > only >> > '01/01/2006' without the time. One of my dates is generated by an >> > expression >> > where I simply add a minus sign and a number, hence the -7, after >> > whatever >> > datefield I wish to show a date 7 days less. Here is an exact example: >> > >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, SplECD - 7 >> > AS >> > Expr2 >> > FROM dbo.tbljob_info >> > >> > This works fine until I try to convert Expr2. My problem is that I can >> > not >> > figure out how to get the -7 *and* the CONVERT to work. >> > >> > >> > "Sylvain Lafontaine" wrote: >> > >> >> Sorry, but I'm unable to reproduce your problem. Could you be more >> >> explicit >> >> about your exact problem? >> >> >> >> Are-you trying to subtract -7 *after* the conversion to nvarchar(10)? >> >> >> >> -- >> >> Sylvain Lafontaine, ing. >> >> MVP - Technologies Virtual-PC >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... >> >> > Greetings everyone. I use the following to convert my dates to text >> >> > format: >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with >> >> > tbljob_info.SplECD >> >> > being smalldatetime. Basically it just lops off the time. It works >> >> > fine, >> >> > but my problem is this. For one of the dates in my query I enter >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It >> >> > also >> >> > works >> >> > fine, but I can not figure out the syntax to combine the CONVERT >> >> > expression >> >> > with the -7. Thanks for any help. >> >> > >> >> >> >> >> >> >> >> >>
|
|
SplECD(smalldatetime) is the name of a field in tbljob_info. In ADP I am
creating a new view, then entering the expression in the select statement.
Are you trying it with ADP? If not maybe the problem is with Access.
"Sylvain Lafontaine" wrote:
[Quoted Text] > Are you sure that SplECD is a datetime or a smalldatetime? I've just created > a table with a smalldatetime field and no problem using something like > CONVERT (nvarchar(10), SplECD - 7, 101). > > Is SplECD the name of the field in the table or an alias? > > How and where are you using this expression exactly? In a Select statement > or somewhere else? > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... > > Yes. This is what I have tried, and the problems with them: > > CONVERT (nvarchar(10), SplECD - 7, 101) > > Error 'Data type error in expression' > > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) > > Error same as above > > CONVERT (nvarchar(10), SplECD, 101)-7 > > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', and > > returns only the expression text. > > CONVERT((nvarchar(10), SplECD, 101) - 7) > > Error 'Invalid or missing expression' > > > > It seems like it must be something so simple. > > > > > > > > > > > > "Sylvain Lafontaine" wrote: > > > >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? > >> > >> -- > >> Sylvain Lafontaine, ing. > >> MVP - Technologies Virtual-PC > >> E-mail: http://cerbermail.com/?QugbLEWINF> >> > >> > >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... > >> > Every night a dts package executes to send a text file to another > >> > server. > >> > My > >> > problem is the dates show up on the text file like this '01/01/2006 > >> > 12:00'. > >> > The administrator of the other server tells me I need to lop of the > >> > time > >> > and > >> > send only the date. When I run the CONVERT command below it returns > >> > only > >> > '01/01/2006' without the time. One of my dates is generated by an > >> > expression > >> > where I simply add a minus sign and a number, hence the -7, after > >> > whatever > >> > datefield I wish to show a date 7 days less. Here is an exact example: > >> > > >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, SplECD - 7 > >> > AS > >> > Expr2 > >> > FROM dbo.tbljob_info > >> > > >> > This works fine until I try to convert Expr2. My problem is that I can > >> > not > >> > figure out how to get the -7 *and* the CONVERT to work. > >> > > >> > > >> > "Sylvain Lafontaine" wrote: > >> > > >> >> Sorry, but I'm unable to reproduce your problem. Could you be more > >> >> explicit > >> >> about your exact problem? > >> >> > >> >> Are-you trying to subtract -7 *after* the conversion to nvarchar(10)? > >> >> > >> >> -- > >> >> Sylvain Lafontaine, ing. > >> >> MVP - Technologies Virtual-PC > >> >> E-mail: http://cerbermail.com/?QugbLEWINF> >> >> > >> >> > >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... > >> >> > Greetings everyone. I use the following to convert my dates to text > >> >> > format: > >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with > >> >> > tbljob_info.SplECD > >> >> > being smalldatetime. Basically it just lops off the time. It works > >> >> > fine, > >> >> > but my problem is this. For one of the dates in my query I enter > >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It > >> >> > also > >> >> > works > >> >> > fine, but I can not figure out the syntax to combine the CONVERT > >> >> > expression > >> >> > with the -7. Thanks for any help. > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
Oh, you are creating a view directly under ADP?
You're more lucky than me: on my side, I'm not even able to create any view,
with or without a datetime expression.
I'm afraid that you will have to use the Enterprise Manager to do this.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:9463BBAF-8B54-4059-B292-6D0056229DBD[ at ]microsoft.com...
[Quoted Text] > SplECD(smalldatetime) is the name of a field in tbljob_info. In ADP I am > creating a new view, then entering the expression in the select statement. > Are you trying it with ADP? If not maybe the problem is with Access. > > "Sylvain Lafontaine" wrote: > >> Are you sure that SplECD is a datetime or a smalldatetime? I've just >> created >> a table with a smalldatetime field and no problem using something like >> CONVERT (nvarchar(10), SplECD - 7, 101). >> >> Is SplECD the name of the field in the table or an alias? >> >> How and where are you using this expression exactly? In a Select >> statement >> or somewhere else? >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... >> > Yes. This is what I have tried, and the problems with them: >> > CONVERT (nvarchar(10), SplECD - 7, 101) >> > Error 'Data type error in expression' >> > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) >> > Error same as above >> > CONVERT (nvarchar(10), SplECD, 101)-7 >> > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', and >> > returns only the expression text. >> > CONVERT((nvarchar(10), SplECD, 101) - 7) >> > Error 'Invalid or missing expression' >> > >> > It seems like it must be something so simple. >> > >> > >> > >> > >> > >> > "Sylvain Lafontaine" wrote: >> > >> >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? >> >> >> >> -- >> >> Sylvain Lafontaine, ing. >> >> MVP - Technologies Virtual-PC >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... >> >> > Every night a dts package executes to send a text file to another >> >> > server. >> >> > My >> >> > problem is the dates show up on the text file like this '01/01/2006 >> >> > 12:00'. >> >> > The administrator of the other server tells me I need to lop of the >> >> > time >> >> > and >> >> > send only the date. When I run the CONVERT command below it returns >> >> > only >> >> > '01/01/2006' without the time. One of my dates is generated by an >> >> > expression >> >> > where I simply add a minus sign and a number, hence the -7, after >> >> > whatever >> >> > datefield I wish to show a date 7 days less. Here is an exact >> >> > example: >> >> > >> >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, >> >> > SplECD - 7 >> >> > AS >> >> > Expr2 >> >> > FROM dbo.tbljob_info >> >> > >> >> > This works fine until I try to convert Expr2. My problem is that I >> >> > can >> >> > not >> >> > figure out how to get the -7 *and* the CONVERT to work. >> >> > >> >> > >> >> > "Sylvain Lafontaine" wrote: >> >> > >> >> >> Sorry, but I'm unable to reproduce your problem. Could you be more >> >> >> explicit >> >> >> about your exact problem? >> >> >> >> >> >> Are-you trying to subtract -7 *after* the conversion to >> >> >> nvarchar(10)? >> >> >> >> >> >> -- >> >> >> Sylvain Lafontaine, ing. >> >> >> MVP - Technologies Virtual-PC >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >> >> >> message >> >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... >> >> >> > Greetings everyone. I use the following to convert my dates to >> >> >> > text >> >> >> > format: >> >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with >> >> >> > tbljob_info.SplECD >> >> >> > being smalldatetime. Basically it just lops off the time. It >> >> >> > works >> >> >> > fine, >> >> >> > but my problem is this. For one of the dates in my query I enter >> >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It >> >> >> > also >> >> >> > works >> >> >> > fine, but I can not figure out the syntax to combine the CONVERT >> >> >> > expression >> >> >> > with the -7. Thanks for any help. >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>
|
|
Greg,
Ity's hard to say what's wrong, without seeing what you tried.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:EB7D9EDC-3549-4022-A4C3-30845AB6EF51[ at ]microsoft.com...
[Quoted Text] > Graham, when I try this I get the error message 'Poorly formed cast
> function'
>
> "Graham R Seach" wrote:
>
>> Greg,
>>
>> Try using Cast instead of Convert. Cast does the conversion without
>> losing
>> scale.
>>
>> Regards,
>> Graham R Seach
>> Microsoft Access MVP
>> Sydney, Australia
>> ---------------------------
>>
>> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
>> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com...
>> > Greetings everyone. I use the following to convert my dates to text
>> > format:
>> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with
>> > tbljob_info.SplECD
>> > being smalldatetime. Basically it just lops off the time. It works
>> > fine,
>> > but my problem is this. For one of the dates in my query I enter
>> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It also
>> > works
>> > fine, but I can not figure out the syntax to combine the CONVERT
>> > expression
>> > with the -7. Thanks for any help.
>> >
>>
>>
>>
|
|
Thanks for all your help Sylvain where ever you are I hope you are
appreciated. I tried typing the SQL directly into Enterprize Manager, but I
still get the 'Datatype error in expression' message when I try this: CONVERT
(nvarchar(10), SplECD - 7, 101). Any ideas? I have tried putting the
parentheses in all possible positions, but to no avail. Also, what does the
'101' do?
"Sylvain Lafontaine" wrote:
[Quoted Text] > Oh, you are creating a view directly under ADP? > > You're more lucky than me: on my side, I'm not even able to create any view, > with or without a datetime expression. > > I'm afraid that you will have to use the Enterprise Manager to do this. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > news:9463BBAF-8B54-4059-B292-6D0056229DBD[ at ]microsoft.com... > > SplECD(smalldatetime) is the name of a field in tbljob_info. In ADP I am > > creating a new view, then entering the expression in the select statement. > > Are you trying it with ADP? If not maybe the problem is with Access. > > > > "Sylvain Lafontaine" wrote: > > > >> Are you sure that SplECD is a datetime or a smalldatetime? I've just > >> created > >> a table with a smalldatetime field and no problem using something like > >> CONVERT (nvarchar(10), SplECD - 7, 101). > >> > >> Is SplECD the name of the field in the table or an alias? > >> > >> How and where are you using this expression exactly? In a Select > >> statement > >> or somewhere else? > >> > >> -- > >> Sylvain Lafontaine, ing. > >> MVP - Technologies Virtual-PC > >> E-mail: http://cerbermail.com/?QugbLEWINF> >> > >> > >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >> news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... > >> > Yes. This is what I have tried, and the problems with them: > >> > CONVERT (nvarchar(10), SplECD - 7, 101) > >> > Error 'Data type error in expression' > >> > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) > >> > Error same as above > >> > CONVERT (nvarchar(10), SplECD, 101)-7 > >> > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', and > >> > returns only the expression text. > >> > CONVERT((nvarchar(10), SplECD, 101) - 7) > >> > Error 'Invalid or missing expression' > >> > > >> > It seems like it must be something so simple. > >> > > >> > > >> > > >> > > >> > > >> > "Sylvain Lafontaine" wrote: > >> > > >> >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? > >> >> > >> >> -- > >> >> Sylvain Lafontaine, ing. > >> >> MVP - Technologies Virtual-PC > >> >> E-mail: http://cerbermail.com/?QugbLEWINF> >> >> > >> >> > >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >> >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... > >> >> > Every night a dts package executes to send a text file to another > >> >> > server. > >> >> > My > >> >> > problem is the dates show up on the text file like this '01/01/2006 > >> >> > 12:00'. > >> >> > The administrator of the other server tells me I need to lop of the > >> >> > time > >> >> > and > >> >> > send only the date. When I run the CONVERT command below it returns > >> >> > only > >> >> > '01/01/2006' without the time. One of my dates is generated by an > >> >> > expression > >> >> > where I simply add a minus sign and a number, hence the -7, after > >> >> > whatever > >> >> > datefield I wish to show a date 7 days less. Here is an exact > >> >> > example: > >> >> > > >> >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, > >> >> > SplECD - 7 > >> >> > AS > >> >> > Expr2 > >> >> > FROM dbo.tbljob_info > >> >> > > >> >> > This works fine until I try to convert Expr2. My problem is that I > >> >> > can > >> >> > not > >> >> > figure out how to get the -7 *and* the CONVERT to work. > >> >> > > >> >> > > >> >> > "Sylvain Lafontaine" wrote: > >> >> > > >> >> >> Sorry, but I'm unable to reproduce your problem. Could you be more > >> >> >> explicit > >> >> >> about your exact problem? > >> >> >> > >> >> >> Are-you trying to subtract -7 *after* the conversion to > >> >> >> nvarchar(10)? > >> >> >> > >> >> >> -- > >> >> >> Sylvain Lafontaine, ing. > >> >> >> MVP - Technologies Virtual-PC > >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF> >> >> >> > >> >> >> > >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in > >> >> >> message > >> >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... > >> >> >> > Greetings everyone. I use the following to convert my dates to > >> >> >> > text > >> >> >> > format: > >> >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with > >> >> >> > tbljob_info.SplECD > >> >> >> > being smalldatetime. Basically it just lops off the time. It > >> >> >> > works > >> >> >> > fine, > >> >> >> > but my problem is this. For one of the dates in my query I enter > >> >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. It > >> >> >> > also > >> >> >> > works > >> >> >> > fine, but I can not figure out the syntax to combine the CONVERT > >> >> >> > expression > >> >> >> > with the -7. Thanks for any help. > >> >> >> > > >> >> >> > >> >> >> > >> >> >> > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
What's the exact data type of SpIECD? Is it from a table or from a view?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:0452E6A5-14DD-46EE-AAC5-D7C9865706EF[ at ]microsoft.com...
[Quoted Text] > Thanks for all your help Sylvain where ever you are I hope you are > appreciated. I tried typing the SQL directly into Enterprize Manager, but > I > still get the 'Datatype error in expression' message when I try this: > CONVERT > (nvarchar(10), SplECD - 7, 101). Any ideas? I have tried putting the > parentheses in all possible positions, but to no avail. Also, what does > the > '101' do? > > "Sylvain Lafontaine" wrote: > >> Oh, you are creating a view directly under ADP? >> >> You're more lucky than me: on my side, I'm not even able to create any >> view, >> with or without a datetime expression. >> >> I'm afraid that you will have to use the Enterprise Manager to do this. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> news:9463BBAF-8B54-4059-B292-6D0056229DBD[ at ]microsoft.com... >> > SplECD(smalldatetime) is the name of a field in tbljob_info. In ADP I >> > am >> > creating a new view, then entering the expression in the select >> > statement. >> > Are you trying it with ADP? If not maybe the problem is with Access. >> > >> > "Sylvain Lafontaine" wrote: >> > >> >> Are you sure that SplECD is a datetime or a smalldatetime? I've just >> >> created >> >> a table with a smalldatetime field and no problem using something like >> >> CONVERT (nvarchar(10), SplECD - 7, 101). >> >> >> >> Is SplECD the name of the field in the table or an alias? >> >> >> >> How and where are you using this expression exactly? In a Select >> >> statement >> >> or somewhere else? >> >> >> >> -- >> >> Sylvain Lafontaine, ing. >> >> MVP - Technologies Virtual-PC >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> >> news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... >> >> > Yes. This is what I have tried, and the problems with them: >> >> > CONVERT (nvarchar(10), SplECD - 7, 101) >> >> > Error 'Data type error in expression' >> >> > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) >> >> > Error same as above >> >> > CONVERT (nvarchar(10), SplECD, 101)-7 >> >> > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', >> >> > and >> >> > returns only the expression text. >> >> > CONVERT((nvarchar(10), SplECD, 101) - 7) >> >> > Error 'Invalid or missing expression' >> >> > >> >> > It seems like it must be something so simple. >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > "Sylvain Lafontaine" wrote: >> >> > >> >> >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? >> >> >> >> >> >> -- >> >> >> Sylvain Lafontaine, ing. >> >> >> MVP - Technologies Virtual-PC >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >> >> >> message >> >> >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... >> >> >> > Every night a dts package executes to send a text file to another >> >> >> > server. >> >> >> > My >> >> >> > problem is the dates show up on the text file like this >> >> >> > '01/01/2006 >> >> >> > 12:00'. >> >> >> > The administrator of the other server tells me I need to lop of >> >> >> > the >> >> >> > time >> >> >> > and >> >> >> > send only the date. When I run the CONVERT command below it >> >> >> > returns >> >> >> > only >> >> >> > '01/01/2006' without the time. One of my dates is generated by >> >> >> > an >> >> >> > expression >> >> >> > where I simply add a minus sign and a number, hence the -7, after >> >> >> > whatever >> >> >> > datefield I wish to show a date 7 days less. Here is an exact >> >> >> > example: >> >> >> > >> >> >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, >> >> >> > SplECD - 7 >> >> >> > AS >> >> >> > Expr2 >> >> >> > FROM dbo.tbljob_info >> >> >> > >> >> >> > This works fine until I try to convert Expr2. My problem is that >> >> >> > I >> >> >> > can >> >> >> > not >> >> >> > figure out how to get the -7 *and* the CONVERT to work. >> >> >> > >> >> >> > >> >> >> > "Sylvain Lafontaine" wrote: >> >> >> > >> >> >> >> Sorry, but I'm unable to reproduce your problem. Could you be >> >> >> >> more >> >> >> >> explicit >> >> >> >> about your exact problem? >> >> >> >> >> >> >> >> Are-you trying to subtract -7 *after* the conversion to >> >> >> >> nvarchar(10)? >> >> >> >> >> >> >> >> -- >> >> >> >> Sylvain Lafontaine, ing. >> >> >> >> MVP - Technologies Virtual-PC >> >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> >> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >> >> >> >> message >> >> >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... >> >> >> >> > Greetings everyone. I use the following to convert my dates >> >> >> >> > to >> >> >> >> > text >> >> >> >> > format: >> >> >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with >> >> >> >> > tbljob_info.SplECD >> >> >> >> > being smalldatetime. Basically it just lops off the time. It >> >> >> >> > works >> >> >> >> > fine, >> >> >> >> > but my problem is this. For one of the dates in my query I >> >> >> >> > enter >> >> >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. >> >> >> >> > It >> >> >> >> > also >> >> >> >> > works >> >> >> >> > fine, but I can not figure out the syntax to combine the >> >> >> >> > CONVERT >> >> >> >> > expression >> >> >> >> > with the -7. Thanks for any help. >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>
|
|
What's the exact data type of SpIECD as given by the Enterprise Manager when
the table is in design mode?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:0452E6A5-14DD-46EE-AAC5-D7C9865706EF[ at ]microsoft.com...
[Quoted Text] > Thanks for all your help Sylvain where ever you are I hope you are > appreciated. I tried typing the SQL directly into Enterprize Manager, but > I > still get the 'Datatype error in expression' message when I try this: > CONVERT > (nvarchar(10), SplECD - 7, 101). Any ideas? I have tried putting the > parentheses in all possible positions, but to no avail. Also, what does > the > '101' do? > > "Sylvain Lafontaine" wrote: > >> Oh, you are creating a view directly under ADP? >> >> You're more lucky than me: on my side, I'm not even able to create any >> view, >> with or without a datetime expression. >> >> I'm afraid that you will have to use the Enterprise Manager to do this. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> news:9463BBAF-8B54-4059-B292-6D0056229DBD[ at ]microsoft.com... >> > SplECD(smalldatetime) is the name of a field in tbljob_info. In ADP I >> > am >> > creating a new view, then entering the expression in the select >> > statement. >> > Are you trying it with ADP? If not maybe the problem is with Access. >> > >> > "Sylvain Lafontaine" wrote: >> > >> >> Are you sure that SplECD is a datetime or a smalldatetime? I've just >> >> created >> >> a table with a smalldatetime field and no problem using something like >> >> CONVERT (nvarchar(10), SplECD - 7, 101). >> >> >> >> Is SplECD the name of the field in the table or an alias? >> >> >> >> How and where are you using this expression exactly? In a Select >> >> statement >> >> or somewhere else? >> >> >> >> -- >> >> Sylvain Lafontaine, ing. >> >> MVP - Technologies Virtual-PC >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> >> news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... >> >> > Yes. This is what I have tried, and the problems with them: >> >> > CONVERT (nvarchar(10), SplECD - 7, 101) >> >> > Error 'Data type error in expression' >> >> > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) >> >> > Error same as above >> >> > CONVERT (nvarchar(10), SplECD, 101)-7 >> >> > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', >> >> > and >> >> > returns only the expression text. >> >> > CONVERT((nvarchar(10), SplECD, 101) - 7) >> >> > Error 'Invalid or missing expression' >> >> > >> >> > It seems like it must be something so simple. >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > "Sylvain Lafontaine" wrote: >> >> > >> >> >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? >> >> >> >> >> >> -- >> >> >> Sylvain Lafontaine, ing. >> >> >> MVP - Technologies Virtual-PC >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >> >> >> message >> >> >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... >> >> >> > Every night a dts package executes to send a text file to another >> >> >> > server. >> >> >> > My >> >> >> > problem is the dates show up on the text file like this >> >> >> > '01/01/2006 >> >> >> > 12:00'. >> >> >> > The administrator of the other server tells me I need to lop of >> >> >> > the >> >> >> > time >> >> >> > and >> >> >> > send only the date. When I run the CONVERT command below it >> >> >> > returns >> >> >> > only >> >> >> > '01/01/2006' without the time. One of my dates is generated by >> >> >> > an >> >> >> > expression >> >> >> > where I simply add a minus sign and a number, hence the -7, after >> >> >> > whatever >> >> >> > datefield I wish to show a date 7 days less. Here is an exact >> >> >> > example: >> >> >> > >> >> >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, >> >> >> > SplECD - 7 >> >> >> > AS >> >> >> > Expr2 >> >> >> > FROM dbo.tbljob_info >> >> >> > >> >> >> > This works fine until I try to convert Expr2. My problem is that >> >> >> > I >> >> >> > can >> >> >> > not >> >> >> > figure out how to get the -7 *and* the CONVERT to work. >> >> >> > >> >> >> > >> >> >> > "Sylvain Lafontaine" wrote: >> >> >> > >> >> >> >> Sorry, but I'm unable to reproduce your problem. Could you be >> >> >> >> more >> >> >> >> explicit >> >> >> >> about your exact problem? >> >> >> >> >> >> >> >> Are-you trying to subtract -7 *after* the conversion to >> >> >> >> nvarchar(10)? >> >> >> >> >> >> >> >> -- >> >> >> >> Sylvain Lafontaine, ing. >> >> >> >> MVP - Technologies Virtual-PC >> >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> >> >> >> >> >> >> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >> >> >> >> message >> >> >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... >> >> >> >> > Greetings everyone. I use the following to convert my dates >> >> >> >> > to >> >> >> >> > text >> >> >> >> > format: >> >> >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with >> >> >> >> > tbljob_info.SplECD >> >> >> >> > being smalldatetime. Basically it just lops off the time. It >> >> >> >> > works >> >> >> >> > fine, >> >> >> >> > but my problem is this. For one of the dates in my query I >> >> >> >> > enter >> >> >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. >> >> >> >> > It >> >> >> >> > also >> >> >> >> > works >> >> >> >> > fine, but I can not figure out the syntax to combine the >> >> >> >> > CONVERT >> >> >> >> > expression >> >> >> >> > with the -7. Thanks for any help. >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>
|
|
Te be more clear: are you sure that SpIECD is really of type SmallDateTime
as defined by SQL-Server and not of type varchar or nvarchar or something
else?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23yiJrvM3GHA.1608[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > What's the exact data type of SpIECD as given by the Enterprise Manager > when the table is in design mode? > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > news:0452E6A5-14DD-46EE-AAC5-D7C9865706EF[ at ]microsoft.com... >> Thanks for all your help Sylvain where ever you are I hope you are >> appreciated. I tried typing the SQL directly into Enterprize Manager, >> but I >> still get the 'Datatype error in expression' message when I try this: >> CONVERT >> (nvarchar(10), SplECD - 7, 101). Any ideas? I have tried putting the >> parentheses in all possible positions, but to no avail. Also, what does >> the >> '101' do? >> >> "Sylvain Lafontaine" wrote: >> >>> Oh, you are creating a view directly under ADP? >>> >>> You're more lucky than me: on my side, I'm not even able to create any >>> view, >>> with or without a datetime expression. >>> >>> I'm afraid that you will have to use the Enterprise Manager to do this. >>> >>> -- >>> Sylvain Lafontaine, ing. >>> MVP - Technologies Virtual-PC >>> E-mail: http://cerbermail.com/?QugbLEWINF>>> >>> >>> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >>> news:9463BBAF-8B54-4059-B292-6D0056229DBD[ at ]microsoft.com... >>> > SplECD(smalldatetime) is the name of a field in tbljob_info. In ADP I >>> > am >>> > creating a new view, then entering the expression in the select >>> > statement. >>> > Are you trying it with ADP? If not maybe the problem is with Access. >>> > >>> > "Sylvain Lafontaine" wrote: >>> > >>> >> Are you sure that SplECD is a datetime or a smalldatetime? I've just >>> >> created >>> >> a table with a smalldatetime field and no problem using something >>> >> like >>> >> CONVERT (nvarchar(10), SplECD - 7, 101). >>> >> >>> >> Is SplECD the name of the field in the table or an alias? >>> >> >>> >> How and where are you using this expression exactly? In a Select >>> >> statement >>> >> or somewhere else? >>> >> >>> >> -- >>> >> Sylvain Lafontaine, ing. >>> >> MVP - Technologies Virtual-PC >>> >> E-mail: http://cerbermail.com/?QugbLEWINF>>> >> >>> >> >>> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >>> >> news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... >>> >> > Yes. This is what I have tried, and the problems with them: >>> >> > CONVERT (nvarchar(10), SplECD - 7, 101) >>> >> > Error 'Data type error in expression' >>> >> > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) >>> >> > Error same as above >>> >> > CONVERT (nvarchar(10), SplECD, 101)-7 >>> >> > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', >>> >> > and >>> >> > returns only the expression text. >>> >> > CONVERT((nvarchar(10), SplECD, 101) - 7) >>> >> > Error 'Invalid or missing expression' >>> >> > >>> >> > It seems like it must be something so simple. >>> >> > >>> >> > >>> >> > >>> >> > >>> >> > >>> >> > "Sylvain Lafontaine" wrote: >>> >> > >>> >> >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? >>> >> >> >>> >> >> -- >>> >> >> Sylvain Lafontaine, ing. >>> >> >> MVP - Technologies Virtual-PC >>> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>>> >> >> >>> >> >> >>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >>> >> >> message >>> >> >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... >>> >> >> > Every night a dts package executes to send a text file to >>> >> >> > another >>> >> >> > server. >>> >> >> > My >>> >> >> > problem is the dates show up on the text file like this >>> >> >> > '01/01/2006 >>> >> >> > 12:00'. >>> >> >> > The administrator of the other server tells me I need to lop of >>> >> >> > the >>> >> >> > time >>> >> >> > and >>> >> >> > send only the date. When I run the CONVERT command below it >>> >> >> > returns >>> >> >> > only >>> >> >> > '01/01/2006' without the time. One of my dates is generated by >>> >> >> > an >>> >> >> > expression >>> >> >> > where I simply add a minus sign and a number, hence the -7, >>> >> >> > after >>> >> >> > whatever >>> >> >> > datefield I wish to show a date 7 days less. Here is an exact >>> >> >> > example: >>> >> >> > >>> >> >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, >>> >> >> > SplECD - 7 >>> >> >> > AS >>> >> >> > Expr2 >>> >> >> > FROM dbo.tbljob_info >>> >> >> > >>> >> >> > This works fine until I try to convert Expr2. My problem is that >>> >> >> > I >>> >> >> > can >>> >> >> > not >>> >> >> > figure out how to get the -7 *and* the CONVERT to work. >>> >> >> > >>> >> >> > >>> >> >> > "Sylvain Lafontaine" wrote: >>> >> >> > >>> >> >> >> Sorry, but I'm unable to reproduce your problem. Could you be >>> >> >> >> more >>> >> >> >> explicit >>> >> >> >> about your exact problem? >>> >> >> >> >>> >> >> >> Are-you trying to subtract -7 *after* the conversion to >>> >> >> >> nvarchar(10)? >>> >> >> >> >>> >> >> >> -- >>> >> >> >> Sylvain Lafontaine, ing. >>> >> >> >> MVP - Technologies Virtual-PC >>> >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>>> >> >> >> >>> >> >> >> >>> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >>> >> >> >> message >>> >> >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... >>> >> >> >> > Greetings everyone. I use the following to convert my dates >>> >> >> >> > to >>> >> >> >> > text >>> >> >> >> > format: >>> >> >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with >>> >> >> >> > tbljob_info.SplECD >>> >> >> >> > being smalldatetime. Basically it just lops off the time. >>> >> >> >> > It >>> >> >> >> > works >>> >> >> >> > fine, >>> >> >> >> > but my problem is this. For one of the dates in my query I >>> >> >> >> > enter >>> >> >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. >>> >> >> >> > It >>> >> >> >> > also >>> >> >> >> > works >>> >> >> >> > fine, but I can not figure out the syntax to combine the >>> >> >> >> > CONVERT >>> >> >> >> > expression >>> >> >> >> > with the -7. Thanks for any help. >>> >> >> >> > >>> >> >> >> >>> >> >> >> >>> >> >> >> >>> >> >> >>> >> >> >>> >> >> >>> >> >>> >> >>> >> >>> >>> >>> > >
|
|
SplECD is a field of type smalldatetime(4) in a table. I design all my
tables in Enterprize Manager. I have tested this problem on several other
fields and tables, and I always get an error message.
"Sylvain Lafontaine" wrote:
[Quoted Text] > Te be more clear: are you sure that SpIECD is really of type SmallDateTime > as defined by SQL-Server and not of type varchar or nvarchar or something > else? > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> > wrote in message news:%23yiJrvM3GHA.1608[ at ]TK2MSFTNGP04.phx.gbl... > > What's the exact data type of SpIECD as given by the Enterprise Manager > > when the table is in design mode? > > > > -- > > Sylvain Lafontaine, ing. > > MVP - Technologies Virtual-PC > > E-mail: http://cerbermail.com/?QugbLEWINF> > > > > > "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > > news:0452E6A5-14DD-46EE-AAC5-D7C9865706EF[ at ]microsoft.com... > >> Thanks for all your help Sylvain where ever you are I hope you are > >> appreciated. I tried typing the SQL directly into Enterprize Manager, > >> but I > >> still get the 'Datatype error in expression' message when I try this: > >> CONVERT > >> (nvarchar(10), SplECD - 7, 101). Any ideas? I have tried putting the > >> parentheses in all possible positions, but to no avail. Also, what does > >> the > >> '101' do? > >> > >> "Sylvain Lafontaine" wrote: > >> > >>> Oh, you are creating a view directly under ADP? > >>> > >>> You're more lucky than me: on my side, I'm not even able to create any > >>> view, > >>> with or without a datetime expression. > >>> > >>> I'm afraid that you will have to use the Enterprise Manager to do this. > >>> > >>> -- > >>> Sylvain Lafontaine, ing. > >>> MVP - Technologies Virtual-PC > >>> E-mail: http://cerbermail.com/?QugbLEWINF> >>> > >>> > >>> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >>> news:9463BBAF-8B54-4059-B292-6D0056229DBD[ at ]microsoft.com... > >>> > SplECD(smalldatetime) is the name of a field in tbljob_info. In ADP I > >>> > am > >>> > creating a new view, then entering the expression in the select > >>> > statement. > >>> > Are you trying it with ADP? If not maybe the problem is with Access. > >>> > > >>> > "Sylvain Lafontaine" wrote: > >>> > > >>> >> Are you sure that SplECD is a datetime or a smalldatetime? I've just > >>> >> created > >>> >> a table with a smalldatetime field and no problem using something > >>> >> like > >>> >> CONVERT (nvarchar(10), SplECD - 7, 101). > >>> >> > >>> >> Is SplECD the name of the field in the table or an alias? > >>> >> > >>> >> How and where are you using this expression exactly? In a Select > >>> >> statement > >>> >> or somewhere else? > >>> >> > >>> >> -- > >>> >> Sylvain Lafontaine, ing. > >>> >> MVP - Technologies Virtual-PC > >>> >> E-mail: http://cerbermail.com/?QugbLEWINF> >>> >> > >>> >> > >>> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >>> >> news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... > >>> >> > Yes. This is what I have tried, and the problems with them: > >>> >> > CONVERT (nvarchar(10), SplECD - 7, 101) > >>> >> > Error 'Data type error in expression' > >>> >> > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) > >>> >> > Error same as above > >>> >> > CONVERT (nvarchar(10), SplECD, 101)-7 > >>> >> > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', > >>> >> > and > >>> >> > returns only the expression text. > >>> >> > CONVERT((nvarchar(10), SplECD, 101) - 7) > >>> >> > Error 'Invalid or missing expression' > >>> >> > > >>> >> > It seems like it must be something so simple. > >>> >> > > >>> >> > > >>> >> > > >>> >> > > >>> >> > > >>> >> > "Sylvain Lafontaine" wrote: > >>> >> > > >>> >> >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? > >>> >> >> > >>> >> >> -- > >>> >> >> Sylvain Lafontaine, ing. > >>> >> >> MVP - Technologies Virtual-PC > >>> >> >> E-mail: http://cerbermail.com/?QugbLEWINF> >>> >> >> > >>> >> >> > >>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in > >>> >> >> message > >>> >> >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... > >>> >> >> > Every night a dts package executes to send a text file to > >>> >> >> > another > >>> >> >> > server. > >>> >> >> > My > >>> >> >> > problem is the dates show up on the text file like this > >>> >> >> > '01/01/2006 > >>> >> >> > 12:00'. > >>> >> >> > The administrator of the other server tells me I need to lop of > >>> >> >> > the > >>> >> >> > time > >>> >> >> > and > >>> >> >> > send only the date. When I run the CONVERT command below it > >>> >> >> > returns > >>> >> >> > only > >>> >> >> > '01/01/2006' without the time. One of my dates is generated by > >>> >> >> > an > >>> >> >> > expression > >>> >> >> > where I simply add a minus sign and a number, hence the -7, > >>> >> >> > after > >>> >> >> > whatever > >>> >> >> > datefield I wish to show a date 7 days less. Here is an exact > >>> >> >> > example: > >>> >> >> > > >>> >> >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, > >>> >> >> > SplECD - 7 > >>> >> >> > AS > >>> >> >> > Expr2 > >>> >> >> > FROM dbo.tbljob_info > >>> >> >> > > >>> >> >> > This works fine until I try to convert Expr2. My problem is that > >>> >> >> > I > >>> >> >> > can > >>> >> >> > not > >>> >> >> > figure out how to get the -7 *and* the CONVERT to work. > >>> >> >> > > >>> >> >> > > >>> >> >> > "Sylvain Lafontaine" wrote: > >>> >> >> > > >>> >> >> >> Sorry, but I'm unable to reproduce your problem. Could you be > >>> >> >> >> more > >>> >> >> >> explicit > >>> >> >> >> about your exact problem? > >>> >> >> >> > >>> >> >> >> Are-you trying to subtract -7 *after* the conversion to > >>> >> >> >> nvarchar(10)? > >>> >> >> >> > >>> >> >> >> -- > >>> >> >> >> Sylvain Lafontaine, ing. > >>> >> >> >> MVP - Technologies Virtual-PC > >>> >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF> >>> >> >> >> > >>> >> >> >> > >>> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in > >>> >> >> >> message > >>> >> >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... > >>> >> >> >> > Greetings everyone. I use the following to convert my dates > >>> >> >> >> > to > >>> >> >> >> > text > >>> >> >> >> > format: > >>> >> >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with > >>> >> >> >> > tbljob_info.SplECD > >>> >> >> >> > being smalldatetime. Basically it just lops off the time. > >>> >> >> >> > It > >>> >> >> >> > works > >>> >> >> >> > fine, > >>> >> >> >> > but my problem is this. For one of the dates in my query I > >>> >> >> >> > enter > >>> >> >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. > >>> >> >> >> > It > >>> >> >> >> > also > >>> >> >> >> > works > >>> >> >> >> > fine, but I can not figure out the syntax to combine the > >>> >> >> >> > CONVERT > >>> >> >> >> > expression > >>> >> >> >> > with the -7. Thanks for any help. > >>> >> >> >> > > >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > >>> >> >> > >>> >> >> > >>> >> >> > >>> >> > >>> >> > >>> >> > >>> > >>> > >>> > > > > > > > |
|
Sylvain, you are going to think I am crazy, but I am not. After I logged in
to the server to verify the datatype and reply to your last post I thought
while I was there I would try again. It hesitated for a moment, but then it
worked! I then went back to my ADP to type the same query in there and it
worked there as well! Is this not what we have been trying? CONVERT
(nvarchar(10), SplECD - 7, 101). Anyway, it works for now. Have you ever
heard of anything like this happening?
"Sylvain Lafontaine" wrote:
[Quoted Text] > Te be more clear: are you sure that SpIECD is really of type SmallDateTime > as defined by SQL-Server and not of type varchar or nvarchar or something > else? > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF> > > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> > wrote in message news:%23yiJrvM3GHA.1608[ at ]TK2MSFTNGP04.phx.gbl... > > What's the exact data type of SpIECD as given by the Enterprise Manager > > when the table is in design mode? > > > > -- > > Sylvain Lafontaine, ing. > > MVP - Technologies Virtual-PC > > E-mail: http://cerbermail.com/?QugbLEWINF> > > > > > "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > > news:0452E6A5-14DD-46EE-AAC5-D7C9865706EF[ at ]microsoft.com... > >> Thanks for all your help Sylvain where ever you are I hope you are > >> appreciated. I tried typing the SQL directly into Enterprize Manager, > >> but I > >> still get the 'Datatype error in expression' message when I try this: > >> CONVERT > >> (nvarchar(10), SplECD - 7, 101). Any ideas? I have tried putting the > >> parentheses in all possible positions, but to no avail. Also, what does > >> the > >> '101' do? > >> > >> "Sylvain Lafontaine" wrote: > >> > >>> Oh, you are creating a view directly under ADP? > >>> > >>> You're more lucky than me: on my side, I'm not even able to create any > >>> view, > >>> with or without a datetime expression. > >>> > >>> I'm afraid that you will have to use the Enterprise Manager to do this. > >>> > >>> -- > >>> Sylvain Lafontaine, ing. > >>> MVP - Technologies Virtual-PC > >>> E-mail: http://cerbermail.com/?QugbLEWINF> >>> > >>> > >>> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >>> news:9463BBAF-8B54-4059-B292-6D0056229DBD[ at ]microsoft.com... > >>> > SplECD(smalldatetime) is the name of a field in tbljob_info. In ADP I > >>> > am > >>> > creating a new view, then entering the expression in the select > >>> > statement. > >>> > Are you trying it with ADP? If not maybe the problem is with Access. > >>> > > >>> > "Sylvain Lafontaine" wrote: > >>> > > >>> >> Are you sure that SplECD is a datetime or a smalldatetime? I've just > >>> >> created > >>> >> a table with a smalldatetime field and no problem using something > >>> >> like > >>> >> CONVERT (nvarchar(10), SplECD - 7, 101). > >>> >> > >>> >> Is SplECD the name of the field in the table or an alias? > >>> >> > >>> >> How and where are you using this expression exactly? In a Select > >>> >> statement > >>> >> or somewhere else? > >>> >> > >>> >> -- > >>> >> Sylvain Lafontaine, ing. > >>> >> MVP - Technologies Virtual-PC > >>> >> E-mail: http://cerbermail.com/?QugbLEWINF> >>> >> > >>> >> > >>> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message > >>> >> news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... > >>> >> > Yes. This is what I have tried, and the problems with them: > >>> >> > CONVERT (nvarchar(10), SplECD - 7, 101) > >>> >> > Error 'Data type error in expression' > >>> >> > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) > >>> >> > Error same as above > >>> >> > CONVERT (nvarchar(10), SplECD, 101)-7 > >>> >> > No error, but changes to N'CONVERT (nvarchar(10), SplECD, 101)-7', > >>> >> > and > >>> >> > returns only the expression text. > >>> >> > CONVERT((nvarchar(10), SplECD, 101) - 7) > >>> >> > Error 'Invalid or missing expression' > >>> >> > > >>> >> > It seems like it must be something so simple. > >>> >> > > >>> >> > > >>> >> > > >>> >> > > >>> >> > > >>> >> > "Sylvain Lafontaine" wrote: > >>> >> > > >>> >> >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? > >>> >> >> > >>> >> >> -- > >>> >> >> Sylvain Lafontaine, ing. > >>> >> >> MVP - Technologies Virtual-PC > >>> >> >> E-mail: http://cerbermail.com/?QugbLEWINF> >>> >> >> > >>> >> >> > >>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in > >>> >> >> message > >>> >> >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... > >>> >> >> > Every night a dts package executes to send a text file to > >>> >> >> > another > >>> >> >> > server. > >>> >> >> > My > >>> >> >> > problem is the dates show up on the text file like this > >>> >> >> > '01/01/2006 > >>> >> >> > 12:00'. > >>> >> >> > The administrator of the other server tells me I need to lop of > >>> >> >> > the > >>> >> >> > time > >>> >> >> > and > >>> >> >> > send only the date. When I run the CONVERT command below it > >>> >> >> > returns > >>> >> >> > only > >>> >> >> > '01/01/2006' without the time. One of my dates is generated by > >>> >> >> > an > >>> >> >> > expression > >>> >> >> > where I simply add a minus sign and a number, hence the -7, > >>> >> >> > after > >>> >> >> > whatever > >>> >> >> > datefield I wish to show a date 7 days less. Here is an exact > >>> >> >> > example: > >>> >> >> > > >>> >> >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, > >>> >> >> > SplECD - 7 > >>> >> >> > AS > >>> >> >> > Expr2 > >>> >> >> > FROM dbo.tbljob_info > >>> >> >> > > >>> >> >> > This works fine until I try to convert Expr2. My problem is that > >>> >> >> > I > >>> >> >> > can > >>> >> >> > not > >>> >> >> > figure out how to get the -7 *and* the CONVERT to work. > >>> >> >> > > >>> >> >> > > >>> >> >> > "Sylvain Lafontaine" wrote: > >>> >> >> > > >>> >> >> >> Sorry, but I'm unable to reproduce your problem. Could you be > >>> >> >> >> more > >>> >> >> >> explicit > >>> >> >> >> about your exact problem? > >>> >> >> >> > >>> >> >> >> Are-you trying to subtract -7 *after* the conversion to > >>> >> >> >> nvarchar(10)? > >>> >> >> >> > >>> >> >> >> -- > >>> >> >> >> Sylvain Lafontaine, ing. > >>> >> >> >> MVP - Technologies Virtual-PC > >>> >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF> >>> >> >> >> > >>> >> >> >> > >>> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in > >>> >> >> >> message > >>> >> >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... > >>> >> >> >> > Greetings everyone. I use the following to convert my dates > >>> >> >> >> > to > >>> >> >> >> > text > >>> >> >> >> > format: > >>> >> >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with > >>> >> >> >> > tbljob_info.SplECD > >>> >> >> >> > being smalldatetime. Basically it just lops off the time. > >>> >> >> >> > It > >>> >> >> >> > works > >>> >> >> >> > fine, > >>> >> >> >> > but my problem is this. For one of the dates in my query I > >>> >> >> >> > enter > >>> >> >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one week. > >>> >> >> >> > It > >>> >> >> >> > also > >>> >> >> >> > works > >>> >> >> >> > fine, but I can not figure out the syntax to combine the > >>> >> >> >> > CONVERT > >>> >> >> >> > expression > >>> >> >> >> > with the -7. Thanks for any help. > >>> >> >> >> > > >>> >> >> >> > >>> >> >> >> > >>> >> >> >> > >>> >> >> > >>> >> >> > >>> >> >> > >>> >> > >>> >> > >>> >> > >>> > >>> > >>> > > > > > > > |
|
No.
Maybe some corruption problems in the metadata. Did you design this table
in ADP or something like that? If you have a backup, you could try on the
backup.
However, the important point is that it is now working.
Don't forget that it is important to run a refresh command for the list of
tables and one for the list of queries/views/functions or to close/reopen
the ADP project after any change in the backend that could change the values
of the metadata.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:F45EFE56-D965-45FB-B62A-F71FD3AC4936[ at ]microsoft.com...
[Quoted Text] > Sylvain, you are going to think I am crazy, but I am not. After I logged > in > to the server to verify the datatype and reply to your last post I thought > while I was there I would try again. It hesitated for a moment, but then > it > worked! I then went back to my ADP to type the same query in there and it > worked there as well! Is this not what we have been trying? CONVERT > (nvarchar(10), SplECD - 7, 101). Anyway, it works for now. Have you ever > heard of anything like this happening? > > "Sylvain Lafontaine" wrote: > >> Te be more clear: are you sure that SpIECD is really of type >> SmallDateTime >> as defined by SQL-Server and not of type varchar or nvarchar or something >> else? >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF>> >> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> >> wrote in message news:%23yiJrvM3GHA.1608[ at ]TK2MSFTNGP04.phx.gbl... >> > What's the exact data type of SpIECD as given by the Enterprise Manager >> > when the table is in design mode? >> > >> > -- >> > Sylvain Lafontaine, ing. >> > MVP - Technologies Virtual-PC >> > E-mail: http://cerbermail.com/?QugbLEWINF>> > >> > >> > "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> > news:0452E6A5-14DD-46EE-AAC5-D7C9865706EF[ at ]microsoft.com... >> >> Thanks for all your help Sylvain where ever you are I hope you are >> >> appreciated. I tried typing the SQL directly into Enterprize Manager, >> >> but I >> >> still get the 'Datatype error in expression' message when I try this: >> >> CONVERT >> >> (nvarchar(10), SplECD - 7, 101). Any ideas? I have tried putting the >> >> parentheses in all possible positions, but to no avail. Also, what >> >> does >> >> the >> >> '101' do? >> >> >> >> "Sylvain Lafontaine" wrote: >> >> >> >>> Oh, you are creating a view directly under ADP? >> >>> >> >>> You're more lucky than me: on my side, I'm not even able to create >> >>> any >> >>> view, >> >>> with or without a datetime expression. >> >>> >> >>> I'm afraid that you will have to use the Enterprise Manager to do >> >>> this. >> >>> >> >>> -- >> >>> Sylvain Lafontaine, ing. >> >>> MVP - Technologies Virtual-PC >> >>> E-mail: http://cerbermail.com/?QugbLEWINF>> >>> >> >>> >> >>> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message >> >>> news:9463BBAF-8B54-4059-B292-6D0056229DBD[ at ]microsoft.com... >> >>> > SplECD(smalldatetime) is the name of a field in tbljob_info. In >> >>> > ADP I >> >>> > am >> >>> > creating a new view, then entering the expression in the select >> >>> > statement. >> >>> > Are you trying it with ADP? If not maybe the problem is with >> >>> > Access. >> >>> > >> >>> > "Sylvain Lafontaine" wrote: >> >>> > >> >>> >> Are you sure that SplECD is a datetime or a smalldatetime? I've >> >>> >> just >> >>> >> created >> >>> >> a table with a smalldatetime field and no problem using something >> >>> >> like >> >>> >> CONVERT (nvarchar(10), SplECD - 7, 101). >> >>> >> >> >>> >> Is SplECD the name of the field in the table or an alias? >> >>> >> >> >>> >> How and where are you using this expression exactly? In a Select >> >>> >> statement >> >>> >> or somewhere else? >> >>> >> >> >>> >> -- >> >>> >> Sylvain Lafontaine, ing. >> >>> >> MVP - Technologies Virtual-PC >> >>> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >>> >> >> >>> >> >> >>> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >> >>> >> message >> >>> >> news:616810B0-C315-403F-A20E-EBC48F56ADA1[ at ]microsoft.com... >> >>> >> > Yes. This is what I have tried, and the problems with them: >> >>> >> > CONVERT (nvarchar(10), SplECD - 7, 101) >> >>> >> > Error 'Data type error in expression' >> >>> >> > CONVERT (nvarchar(10), SplECD - 7 AS Expr2, 101) >> >>> >> > Error same as above >> >>> >> > CONVERT (nvarchar(10), SplECD, 101)-7 >> >>> >> > No error, but changes to N'CONVERT (nvarchar(10), SplECD, >> >>> >> > 101)-7', >> >>> >> > and >> >>> >> > returns only the expression text. >> >>> >> > CONVERT((nvarchar(10), SplECD, 101) - 7) >> >>> >> > Error 'Invalid or missing expression' >> >>> >> > >> >>> >> > It seems like it must be something so simple. >> >>> >> > >> >>> >> > >> >>> >> > >> >>> >> > >> >>> >> > >> >>> >> > "Sylvain Lafontaine" wrote: >> >>> >> > >> >>> >> >> Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ? >> >>> >> >> >> >>> >> >> -- >> >>> >> >> Sylvain Lafontaine, ing. >> >>> >> >> MVP - Technologies Virtual-PC >> >>> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >>> >> >> >> >>> >> >> >> >>> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in >> >>> >> >> message >> >>> >> >> news:24B08E7A-AB59-4E9D-8146-67E01C02FEC9[ at ]microsoft.com... >> >>> >> >> > Every night a dts package executes to send a text file to >> >>> >> >> > another >> >>> >> >> > server. >> >>> >> >> > My >> >>> >> >> > problem is the dates show up on the text file like this >> >>> >> >> > '01/01/2006 >> >>> >> >> > 12:00'. >> >>> >> >> > The administrator of the other server tells me I need to lop >> >>> >> >> > of >> >>> >> >> > the >> >>> >> >> > time >> >>> >> >> > and >> >>> >> >> > send only the date. When I run the CONVERT command below it >> >>> >> >> > returns >> >>> >> >> > only >> >>> >> >> > '01/01/2006' without the time. One of my dates is generated >> >>> >> >> > by >> >>> >> >> > an >> >>> >> >> > expression >> >>> >> >> > where I simply add a minus sign and a number, hence the -7, >> >>> >> >> > after >> >>> >> >> > whatever >> >>> >> >> > datefield I wish to show a date 7 days less. Here is an >> >>> >> >> > exact >> >>> >> >> > example: >> >>> >> >> > >> >>> >> >> > SELECT EWO, CONVERT(nvarchar(10), SplECD, 101) AS Expr1, >> >>> >> >> > SplECD - 7 >> >>> >> >> > AS >> >>> >> >> > Expr2 >> >>> >> >> > FROM dbo.tbljob_info >> >>> >> >> > >> >>> >> >> > This works fine until I try to convert Expr2. My problem is >> >>> >> >> > that >> >>> >> >> > I >> >>> >> >> > can >> >>> >> >> > not >> >>> >> >> > figure out how to get the -7 *and* the CONVERT to work. >> >>> >> >> > >> >>> >> >> > >> >>> >> >> > "Sylvain Lafontaine" wrote: >> >>> >> >> > >> >>> >> >> >> Sorry, but I'm unable to reproduce your problem. Could you >> >>> >> >> >> be >> >>> >> >> >> more >> >>> >> >> >> explicit >> >>> >> >> >> about your exact problem? >> >>> >> >> >> >> >>> >> >> >> Are-you trying to subtract -7 *after* the conversion to >> >>> >> >> >> nvarchar(10)? >> >>> >> >> >> >> >>> >> >> >> -- >> >>> >> >> >> Sylvain Lafontaine, ing. >> >>> >> >> >> MVP - Technologies Virtual-PC >> >>> >> >> >> E-mail: http://cerbermail.com/?QugbLEWINF>> >>> >> >> >> >> >>> >> >> >> >> >>> >> >> >> "Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote >> >>> >> >> >> in >> >>> >> >> >> message >> >>> >> >> >> news:C319FC70-FCFD-4CAC-8B19-F6CDF37D721A[ at ]microsoft.com... >> >>> >> >> >> > Greetings everyone. I use the following to convert my >> >>> >> >> >> > dates >> >>> >> >> >> > to >> >>> >> >> >> > text >> >>> >> >> >> > format: >> >>> >> >> >> > CONVERT (nvarchar(10), dbo.tbljob_info.SplECD, 101), with >> >>> >> >> >> > tbljob_info.SplECD >> >>> >> >> >> > being smalldatetime. Basically it just lops off the time. >> >>> >> >> >> > It >> >>> >> >> >> > works >> >>> >> >> >> > fine, >> >>> >> >> >> > but my problem is this. For one of the dates in my query >> >>> >> >> >> > I >> >>> >> >> >> > enter >> >>> >> >> >> > 'dbo.tbljob_info.SplECD-7' to show this date minus one >> >>> >> >> >> > week. >> >>> >> >> >> > It >> >>> >> >> >> > also >> >>> >> >> >> > works >> >>> >> >> >> > fine, but I can not figure out the syntax to combine the >> >>> >> >> >> > CONVERT >> >>> >> >> >> > expression >> >>> >> >> >> > with the -7. Thanks for any help. >> >>> >> >> >> > >> >>> >> >> >> >> >>> >> >> >> >> >>> >> >> >> >> >>> >> >> >> >>> >> >> >> >>> >> >> >> >>> >> >> >>> >> >> >>> >> >> >>> >> >>> >> >>> >> > >> > >> >> >> | | |