Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ckb11
Starting Member
2 Posts |
 Posted - 2012-11-07 : 18:23:11
|
| 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-07 : 20:01:10
|
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, or3. 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 exampleCAST(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 castSELECT 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. |
 |
|
|
|
|
|
|
|
Converting 'DD-MON-YY' to datetime