skip to Main Content

Parsing Dates can be Trying

SQL Server and dates are often troublesome. In fact every database server I have ever worked on has had trouble with dates and with the parsing of dates in particular. The issue is that different countries have different date formats and that by looking at certain dates expressed as a string it is not always obvious exactly which date it represents. The classic example is that Americans start dates with the month, whereas Europeans usually start them with the day. So for example:

  • ‘01/02/2011’ to a European means 1 February 2011
  • ‘01/02/2011’ to an American means 2 January 2011

Quite often this results in either run time errors or misinterpreted dates. On my SQL Server I just tried:

  • SELECT CAST(’12/16/2011′ AS datetime) and it returned 16 December 2011
  • SELECT CAST(’16/12/2011′ AS datetime) and it returned a run-time error

Clearly my SQL Server is configured for US dates.

The SET DATEFORMAT dmy command offers an option to override the behaviour and use European dates, in which case the two SELECT statements above would behave differently.

But what if I wanted to check if a given date was a valid European date. I could run:

DECLARE @d datetime

SET @d = CAST(’12/16/2011′ AS datetime)

This would fail with a run-time error because the date is invalid. But a getting a runtime error is hardly a convenient way to process the date and so I would need to add error handling to recover from the error. One way to do is using a TRY CATCH which would look something like this


DECLARE @d datetime


SET @d = CAST(’12/16/2011′ AS datetime)




IF @d IS NULL PRINT ‘Date error’

This is a lot of trouble just to check a date format and so SQL Server Denali (I am using the CTP3 release) has extended T-SQL to make the parsing of dates a lot easier. In particular a new function TRY_PARSE has been added. For example to explicitly parse a date in UK format use:

DECLARE @d datetime

SET @d = TRY_PARSE (’12/16/2011′ AS datetime2 USING ‘en-GB’)

IF @d IS NULL PRINT ‘Date error’

Note that TRY_PARSE will return the date time if the format is good and NULL otherwise. TRY_PARSE is not just for dates and can be used for other country / language specific formats such as currency formats.

There is also a new function called PARSE (without the TRY). This does the same a TRY_PARSE but raises a run-time error if the format is invalid rather than returning NULL.

Finally we also have TRY_CONVERT which is a version of the standard CONVERT function. It too returns NULL if the conversion fails.

All in all we have 3 new T-SQL functions that make the parsing and conversion of data just that little bit easier.

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to [email protected]

About the Author

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).


Back To Top
Contact us for a chat