mssql Casting a Varchar to Datetime

[Nathan]

Sometimes if the database is not designed correctly you will find dates stored as text values. When trying to compare dates to text you will have problems and it's not efficient. So that's where the cast function comes in.

 

Table: Invoice

Field: InvoiceDate

 

Data Set:

20080515

20081025

20070113

20091007

 

Expected Data Set:

05/15/2008

10/25/2008

01/13/2007

10/07/2009

 

To do this simply cast the field as a date:

select cast(InvoiceDate as datetime)
from Invoice

[S.O. Price]

Sometimes if the database is not designed correctly you will find dates stored as text values. When trying to compare dates to text you will have problems and it's not efficient. So that's where the cast function comes in.

 

Table: Invoice

Field: InvoiceDate

 

Data Set:

20080515

20081025

20070113

20091007

 

Expected Data Set:

05/15/2008

10/25/2008

01/13/2007

10/07/2009

 

To do this simply cast the field as a date:

select cast(InvoiceDate as datetime)
from Invoice

 

Thanks for the tip, but I hope I never have to use it! That means I had to take over a database from someone who was probably a beginner. There will probably be more quirks like that elsewhere. On the other hand, sometimes one has to import a file of a different type and in this case 'cast' might come in handy for some fields that need to be converted to datetime..

[Marc]

Another handy one to add to this is the removal of times from datetime. Now there are a few ways of doing this and I prefer not to use convert even though that is what it is made for. Reason? because I dont have to remember the format code

 

 

Dataset

2011-04-06 10:56:00.000

 

Desired

2011-04-06 00:00:00.000

 

 

SELECT CAST(LEFT(InvoiceDate),11) AS DATETIME)
FROM invoice

 

 

If you are interested the other way of doing it is the following

 

SELECT Cast(Convert(varchar, InvoiceDate,105) as datetime)
FROM invoice
[/sQl]