SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Converting 'DD-MON-YY' to datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ckb11
Starting Member

2 Posts

Posted - 11/07/2012 :  18:23:11  Show Profile  Reply with Quote
I need to convert an entire column which is formatted 'DD-MON-YY' into a datetime format, so that I can add and subtract.

Every forum question I have looked up is people trying to convert to 'DD-MON'YY', or they are citing a specific value, or adding the current time. I need to actually convert all the values in my column to the new format.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/07/2012 :  20:01:10  Show Profile  Reply with Quote
quote:
Originally posted by ckb11

I need to convert an entire column which is formatted 'DD-MON-YY' into a datetime format, so that I can add and subtract.

Every forum question I have looked up is people trying to convert to 'DD-MON'YY', or they are citing a specific value, or adding the current time. I need to actually convert all the values in my column to the new format.


If the data type of your column is a character type (VARCHAR for example), even if you convert to datetime and save it back, it would get saved as a character string. So your choices are

1. to leave it as it is (in character format and use that in your calculations, converting to datetime as required) or,

2. Add another column that is of type datetime and then convert the character column to datetime and store in this new column, or

3. Change the data type of the existing column to datetime.

Which of the above 3 do you want to accomplish? Or is it something else?



DD-MON'YY is not a standard format that is usually used, so I am surprised that people are trying to convert that format. Adding/subtracting to current date and other examples people are posting may be just examples that they are posting to show you how to use the functions.



If you have DD-MMM-YY format ( 12-Jan-2012, for example) you can convert that to datetime using cast - for example
CAST(YourCol AS DATETIME);
DD-MMM-YY is one of the standard literals that SQL Server accepts. Once you do that, you can add/subtract as you wish. Example:
SELECT DATEDIFF(dd,CAST(YourCol AS DATETIME),GETDATE()); -- how many days from today.
Since DD-MMM-YY is a standard literal, you don't need to do the explicit cast
SELECT DATEDIFF(dd,YourCol,GETDATE());
Since you have two year format, if your data spans anything earlier than 1950 or later than 2049 it would not work correctly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000