Converting a string to a datetime in SQL Server 2008

Question

Simple question cannot seem to get right.

I have a table with a date in this format 20/09/2012 as varchar.

I need to convert to a datetime

Tried various solutions but cannot get it right.

Any suggestions?

Thanks

Did you try CONVERT(datetime, '2012/09/20 00:00:00', 120) ? Also for date formatting options you can check msdn.microsoft.com/en-us/library/ms187928.aspx — MusicLovingIndianGirl
– MusicLovingIndianGirl, Commented Dec 6, 2013 at 10:16
Using convert(datetime, '20/09/2012', 103) seems to work for me. — jpw
– jpw, Commented Dec 6, 2013 at 10:18

Community · Accepted Answer · 2020-07-29 18:30:16Z

3

Using the CONVERT function with style 103 (British/European) seems to work for me:

DECLARE @input VARCHAR(20) = '20/09/2012'

SELECT
    CONVERT(DATETIME, @input, 103)

With this approach, you could convert all your VARCHAR dates to their proper datatype - DATETIME - and store them as DATETIME in the first place ....

See Aaron Bertrand great blog post on bad habits to kick : mis-handling date / range queries for more background info on why it's really really bad to keep storing dates as VARCHAR columns ....

edited Jul 29, 2020 at 18:30

CommunityBot

11 silver badge

answered Dec 6, 2013 at 10:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Converting a string to a datetime in SQL Server 2008

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related