TSQL : Convert DateTime String to Datetime

You have imported data from a text file into your database which included datetime values formatted as strings. An example is the following string
‘2013-09-12 01:03:52’ which represents the date 12th September 2013 at 3 minutes past 1am on the 52nd second.

The following snippet example show how to convert this string to a datetime value :

DECLARE @string varchar(24), @date date, @time time
SET @string = '2013-09-12 01:03:52'
SELECT REPLACE(LEFT(@string,10),'-','')
SELECT CONVERT(datetime,REPLACE(LEFT(@string,10),'-','')) as DateComponent
SELECT CAST(SUBSTRING(@string,12,9) as time) AS TimeComponent
SELECT CONVERT(datetime,REPLACE(LEFT(@string,10),'-','')) + CAST(SUBSTRING(@string,12,9) as time) as DateTimeDataType

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s