String to DateTime SQL Conversion

Question

I know this has been asked several times but I haven't seen it asked for my particular situation.

If a text was in this format: Dec 30 2006 12:38AM Is there any way to convert this to an actual datetime. I am not excited about creating a function to parse this to a date using substrings, etc.

A previous developer developed the whole application and decided to store all the date values in the database as a VARCHAR which conveniently changed them from something like: 2016-12-30 00:38:00 to Dec 30 2006 12:38AM. This makes them non comareable (SMH)

Gordon Linoff · Accepted Answer · 2018-04-09 17:36:09Z

1

Just use cast():

select cast('Dec 30 2006 12:38AM' as datetime)

Here is a SQL Fiddle.

answered Apr 9, 2018 at 17:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

logixologist Over a year ago

perfect. Not sure why it never worked for me the 1st time

Aaron Bertrand Over a year ago

@logixologist Possibly because it's not language safe. Try set language french; first, for example.

Gordon Linoff Over a year ago

@AaronBertrand . . . I'm amazed by how versatile cast()/convert() are when converting to dates. They understand all sorts of formats that are not otherwise easily parsable

ASP · Accepted Answer · 2018-04-09 17:37:31Z

1

--Please try this

declare @var varchar(30) = 'Dec 30 2006 12:38AM' select convert(datetime, @var)

answered Apr 9, 2018 at 17:37

ASP

6664 silver badges9 bronze badges

Comments

John Cappelletti · Accepted Answer · 2018-04-09 18:03:17Z

1

Since you are converting strings (which is always risky), I would suggest try_convert() if 2012+. Try_Convert() will return a null if the conversion fails rather than throwing an error.

Example

Select ValidDate = try_convert(datetime,'Dec 30 2006 12:38AM')
      ,BogusDate = try_convert(datetime,'Not a Valid Date String')

Returns

ValidDate                BogusDate
2006-12-30 00:38:00.000  NULL

answered Apr 9, 2018 at 18:03

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Collectives™ on Stack Overflow

String to DateTime SQL Conversion

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related