Date function with CONVERT

[Guest]

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.

 

[Kevin Jameson]

you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin

 

[Graham R Seach]

Greg,

Try using Cast instead of Convert. Cast does the conversion without losing
scale.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Sylvain Lafontaine]

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)?

 

[Guest]

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.

 

[Guest]

Graham, when I try this I get the error message 'Poorly formed cast function'

 

[Sylvain Lafontaine]

Did you try « CONVERT(nvarchar(10), SplECD - 7, 101) » ?

 

[Guest]

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]

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?

 

[Guest]

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]

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.

 

[Graham R Seach]

Greg,

Ity's hard to say what's wrong, without seeing what you tried.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Guest]

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]

What's the exact data type of SpIECD? Is it from a table or from a view?

 

[Sylvain Lafontaine]

What's the exact data type of SpIECD as given by the Enterprise Manager when
the table is in design mode?

 

[Sylvain Lafontaine]

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?

 

[Guest]

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.

 

[Guest]

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]

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.