Date function with CONVERT

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Greg,

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

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
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)?
 
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.
 
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.
 
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?
 
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.
 
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.
 
Greg,

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

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
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?
 
What's the exact data type of SpIECD as given by the Enterprise Manager when
the table is in design mode?
 
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?
 
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, 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?
 
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.
 
Back
Top