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:
SET DATEFORMAT dmy
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
SET DATEFORMAT dmy
DECLARE @d datetime
BEGIN TRY
SET @d = CAST(’12/16/2011′ AS datetime)
END TRY
BEGIN CATCH
END CATCH
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]
