How can I convert string to datetime in SQL Server 2008

Question

How can I convert the following:

031413 05:53 AM into DATETIME format?

Where this code:

SELECT CONVERT(DATETIME, '031413 05:53 AM') gives me an error.

Duplicate: http://stackoverflow.com/questions/1135746/sql-server-convert-string-to-datetime — whastupduck, Jun 19 '13 at 06:47
The best solution is usually to avoid having a datetime in a string in the first place. If you keep datetime values in datetime types throughout your code, you don't tend to run into these sort of issues. — Damien_The_Unbeliever, Jun 19 '13 at 06:51

Nithesh Narayanan · Answer 1 · 2013-06-19T07:12:26.703

1

Try This

DECLARE @var VARCHAR(50) ='031613 05:39 AM'

SELECT CONVERT(datetime,SUBSTRING(@var,5,2)+LEFT(@var,2)+SUBSTRING(@var,3,2)+SUBSTRING(@var,7,9))

SQL Fiddle

edited Jun 19 '13 at 07:12

answered Jun 19 '13 at 06:55

Nithesh Narayanan

11,481
34
98
138

score 1 · Answer 2 · answered Jun 19 '13 at 13:20

1

declare @a varchar(40) = '031413 05:53 AM'
select convert(datetime, right(stuff(@a, 7, 0, left(@a, 4)), 15), 109)

Result:

2013-03-14 05:53:00.000

answered Jun 19 '13 at 13:20

t-clausen.dk

43,517
12
59
92

score 0 · Answer 3 · edited Jun 19 '13 at 07:33

0

Try this:

SELECT CONVERT(datetime, SUBSTRING('031413 05:53 AM', 5, 2)
                         + LEFT('031413 05:53 AM',2)
                         + SUBSTRING('031413 05:53 AM', 3, 2)) 
FROM DATES

edited Jun 19 '13 at 07:33

michaelb958--GoFundMonica

4,617
7
31
35

answered Jun 19 '13 at 07:12

Ahmed

452
3
7

How can I convert string to datetime in SQL Server 2008

3 Answers3