CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Jay
Jay

Reputation: 2200

Convert String to datetime in SQL Server 2008

How will I convert date in the format Sat Mar 29 00:00:00 EST 1975 to datetime in TSQL?

I get the date in this format from an old table which defined the date of birth column as NVARCHAR and stores the data as Mon Jun dd hh:mm:ss GMT yyyy format. I need to read another table which has the dob in datetime using this value.

So basically I want to convert, say Sat Mar 29 00:00:00 EST 1975 to 1975-03-29 00:00:00.000

Is there a way in T-SQL to do this conversion? I tried the CONVERT function, but I am unable to locate the correct 'style' to use.

Upvotes: 0

Views: 1608

Answers (1)

Grax32
Grax32

Reputation: 4059

Examining the data format, it appears to be a fixed length string.

The first portion is the day of week, which can be discarded as it isn't needed for parsing. Next you have the month and day information, which we need. After that is the time, which can be retained or discarded depending on whether you want a date or datetime as output.

Since you are looking for a date of birth, the time zone information can most likely be safely discarded.

Finally, there is the year.

If we eliminate the day of week and the time zone, sql server will parse the rest of the string with no problem.

I recommend cast(substring(@difficultTime,5,7) + substring(@difficultTime,25,4) as date), where @difficulteTime is the column name you are converting.

If you wanted to retain the time information, the following format will work cast(substring(@difficultTime,5,16) + substring(@difficultTime,25,4) as datetime)

This assumes that your strings will be of a fixed length. The first conversion shown eliminates the day of week, the time, and the time zone from the string, leaving a parseable date.

The second conversion eliminates the day of week and the time zone, leaving a parseable datetime.

Upvotes: 3

Related Questions