Using CONVERT - String Literals to Date Data Types

The CONVERT function has many uses and in this lesson we focus on just one aspect, converting string literals to various date based data types. CONVERT is amazingly versatile, understanding a wide range of string formats that can be converted to dates, and we'll show you how some of these work. It's hard to work with data and not find a time when you'll need to do these types of conversions and if we could offer one tip it would be to always specifiy the four digit year!

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Aldo on 12/13/2010

great overview - not exactly sure what happens if the locale in not en-US or using a non-Gregorian calendar

David Bridge on 12/13/2010

Although this looked at several issues they are not issues that I have ever had a problem with. A more important issue that I was hoping the video would cover is that of ambiguous dates. e.g. Converting 12/01/2010 to either 12 Janurary or 1st December. Note that when you put a date from a sql in to a list box in .net then read it back in to a reportViewer control (effectively sql again) then it can get this wrong. How do we get around this?

Aldo on 12/13/2010

safely enough, always use the ISO format yyyy-MM-ddThh:mm:ss.fffff

Terrance on 12/13/2010

Very good example; enjoyed it a lot.

anand on 12/13/2010

Good to have knowledge about date conversion. thats small datetime does not consider seconds and it can go upto 2079.

Dick Rosenberg on 12/13/2010

Excellent! I am an experienced SQL Server TSQL programmer and I learned some things I didn't know. I am looking forward to the rest of the series on CONVERT

Jack Pines on 12/13/2010

Something that's always been an unanswered question on my team is how does SQL handle date format differences from around the world. It seems to be doing so automagically but there are rare times when '12/01/2010' gets is meant as January 12, 2010, in the UK, for example.

Nilesh on 12/13/2010

was fine

Michael Sidley on 12/13/2010

It would have been nice if you had included if there are any differences when we use cast instead of convert or what the differences are, maybe the is coming ;)

John on 12/13/2010

I suspect you'll be touching on the constants used for the different date foromats a little later

Derek on 12/13/2010

So it sounds like SQL Server can just pretty much guess in many cases what to do with a date it receives? In Oracle I always specified what format I was expecting the string to come in as (i.e. mm-dd-yyyy, dd-MON-yy, etc).

MartinIsti on 12/13/2010

Nice stuff and quick reaction to viewers needs :) I especially liked the ISO 8601 format! My personal preference is still an 8 digit integer for dates because I suffered last year from different date formats from different source systems where e.g. JOIN based on a dd/mm/yyyy and a mm/dd/yy equation failed. So I usually convert those into int with the help of the year, month and day functions. But that ISO 8601 might be a better (or as good) method!

silas on 12/13/2010

excellet, thanks for sharing

manjeet on 12/13/2010

good video for datetime Concept

111B29D574 on 12/14/2010

good refresher

Jack Hellmuth on 12/15/2010

I am trying a create a view on SQL 2008 and having a problem with the convert syntax: SELECT CONVERT(date, '10/1/2010') AS YearStartDate I get an error, 'Cannot call methods on date'. This syntax works for just a query. I guess it is not recognizing date as a data type. Am I missing something?

Juri Kirillov on 12/27/2010

useful, thanks

Mohamed Irshad on 12/31/2010

very good demo

Carol Hornung on 1/15/2011

Lots of good, real-life examples. Thx!

Doug McGlasson on 4/17/2011

Can you do a follow-up on converting dates to other formats and strings?

Maurice Ivory on 7/15/2011

Good informtion to know.