Jump to content

Recommended Posts

  • Administrators
Posted

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

  • 1 month later...
Posted

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

Posted

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]

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...