Plzzz: Varchar yyyymmdd column and datediff?

[di99lipe]

Hey!
I have a problem with a this query:

SELECT ASSIGNMENTID, VESSEL_ETA,

ISDATE(VESSEL_ETA), DATEDIFF(dd, VESSEL_ETA , VESSEL_ETA) FROM tblTRAN

VESSEL_ETA is a varchar(15) in ms sql 2005 and looks like this 20071010

The ISDATE(VESSEL_ETA) returns a 1 that confirms that it is a valid date.

The DATEDIFF should return days between the two dates(this time 0 days) but it gives an error

"Conversion failed when converting datetime from character string."

If I try

SELECT ASSIGNMENTID, VESSEL_ETA,

ISDATE(VESSEL_ETA), DATEDIFF(dd, '20070101' , '20070120') FROM tblTRAN

it works fine but this should be exactly the same as above.

What causes this? Any ideas are appreciated...i am confused :)

Peeweeee

[jp140768]

DateDiff works on dates, not character fields, so you may need to convert the character field to a date time one.

The following might work for you:
cast(VESSEL_ETA as datetime)
eg
DATEDIFF(dd, cast(VESSEL_ETA as datetime) , cast(VESSEL_ETA as datetime))

Looking at your SQL statement, the difference will always be zero, as you are specifying the same variable twice in your DateDiff function.

HTH

[davide++]

Hi all.

I don't know SqlServer, but in my opinion you've got the error because DATEDIFF works on date fields and VESSEL_ETA is a text field. You should convert VESSEL_ETA from text to date, using something like this:


SELECT ASSIGNMENTID, VESSEL_ETA,
ISDATE(VESSEL_ETA), DATEDIFF(dd, DATE(VESSEL_ETA) , DATE(VESSEL_ETA)) FROM tblTRAN


However, when VESSEL_ETA isn't a valid date the query will fail.

[exterminator]

Why would you store a date data in a varchar field? You can always do the formatting in the queries or on the client side!