Jump to content

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×