SQL Server code to convert datetime field

Typically I have no problem converting a date field imported as varchar in a query, but for whatever reason, I have imported a field and it’s come over as ‘5/06/2009 0:00:00’ instead of the workable ‘5/06/2009’. Does anyone know how to convert this in the query code (I don’t want to go through each flat file source and remove the ‘0:00:00’.

I have tried month(CAST(CAST([datefield] AS varchar(10)) AS datetime)) and I get the error “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.”

If I just month() & year() the field(s), it gives me the following error:

“Error converting data type varchar to numeric.”

Has anyone ever run into this problem with a date-type field?

Any help is certainly appreciated.

[Abused Student]

Have you tried

Format([datefield],"mm/dd/yyyy") ?

[GadgetGeek]

The function might be char()

[TwoStep]

‘5/06/2009 0:00:00’ is actually a SMALLDATETIME field
(DATETIME has milliseconds like ‘5/06/2009 0:00:00.000’ )


What datatype (ie VARCHAR(10), VARCHAR(15), NVARCHAR(10)) is it currently stored as?

[TwoStep]

I talked to a DBA today, SQL Server 2008 has a DATE datatype, but it wont work if the database was developed in SQL Server 2005

[Steve Grondin]

Finally. You'd think they would have realized there are a whole class of data that doesn't care what time of day something is. Is it 4 bytes?

[TwoStep]

DATE is three bytes.

There is also a datatype called TIME (which is just time), it takes 3-5 bytes.

[Steve Grondin]

Originally Posted by TwoStep

DATE is three bytes.

Sweet! I didn't think there was anyone left who's as stingy with space as me!

[magillaG]

You should be able to cast, for example, the string ‘5/06/2009 0:00:00’ :

cast(‘5/06/2009 0:00:00’ as datetime)

There is a good chance your error is because some of the dates are not valid. For example, maybe you 4/31/2009, or something like that.

A brute force method would be to use excel to make a list of all the valid date strings that are possible for you, and check that each string is in that list. There is probably a more clever way, but I don't know what it is.

Incidently you can pick off hte 0:00:00 by doing:
rtrim(left(s, len(s) - 7))