January 7, 2014 at 10:51 am
Hello Everyone
I am still working on the date and time data. Now I have found they are storing one of the columns as a varchar. Horrible!!!
Some of the data that I am working with looks like this:
2013-01-09 11:32:00
2013-02-14 20:22:37.033000000
2013-05-05 23:11:15.013000000
2013-06-14 15:20:36.030000000
2013-01-09 11:29:00
I need to convert this data to a datetime format. I will eventually need to compare the date and time with the current date and time, and as we all know, that cannot currently happen.
But I am not able to figure this one out. The data without the milliseconds are easy 🙂 The data that I am having issues with is the data with the milliseconds are longer than 3 digits. If there are milliseconds, I would like to keep that time, if there are not, not a big thing.
I did not want to setup consumable data, I was afraid of losing the format that I see.
Thank You in advance for all your suggestions, comments and data samples.
Andrew SQLDBA
January 7, 2014 at 11:06 am
You could truncate your values to 23 positions or you could convert them to datetime2
CREATE TABLE #Test(
chardatevarchar( 50))
INSERT #Test VALUES(
'2013-01-09 11:32:00'),(
'2013-02-14 20:22:37.033000000'),(
'2013-05-05 23:11:15.013000000'),(
'2013-06-14 15:20:36.030000000'),(
'2013-01-09 11:29:00')
SELECT *, CAST(LEFT( chardate, 23) AS datetime), CAST(chardate AS datetime2)
FROM #Test
DROP TABLE #Test
January 7, 2014 at 11:39 am
Thank You Luis, that worked perfectly.
Greatly appreciate it.
Andrew SQLDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply