Here we are again, back again with the bane of all programmers lives:-
Dates! and I’m not talking about the kind that makes you run for the toilet or the ones that keep you regular either! There’s only one thing worse than dates and that’s the formatting of addresses which everyone seems to make up their own rules for, bygones….
So this time I have an issue with SQL Server for a change. This actually takes me back to the start of my career and the first programming job I had, working on a trade and settlement system for a corporate bank. It was my first exposure to code, and the companies first exposure to SQL Server. There were many late nights, hair tearing and poring over a keyboard with the more than somewhat faint aroma of rolling tobacco, sweat and men. Predominantly however it smelt of wet english sheepdog, the bosses dog which had decided to park itself under my desk for the duration of the project…. Dates were certainly something to come to terms with and in the end strategies were formed and stayed for years. This was the days before the internet was readily accessible and so knowledge was hard fought and hard won!
Had I known then what I know now…. One simple little statement presented itself to me today as I was dealing within this little difficulty:- I was trying to execute the following SQL:-
SELECT IsDate(TextDate)AS IsADate,TextDate AS TheDate, CASE WHEN IsDate (TextDate)=1 THEN CAST(TextDate AS DATE) ELSE NULL END AS TheDateAsADate FROM TheTableOfDates
Looks simple enough, but the difficulty is this is a database derived from an AS 400 system and so the type checking is …. ummmm, what’s type checking?! We could literally have anything in a date field, in fact, truth be known the field I am looking in is actually not even MEANT to be a date field.
So, this simple piece of pseudo SQL is supposed to derive the text representation of the date, a proper typed date representation of the date (where this is permissible) and then one flag specifying whether the text value is a valid date value that can be converted. This looks nice and correct and without issue but its not, at least not here in the UK on these systems. The results are as follows:-
IsADate TheDate TheDateAsADate 0 13/08/2012 NULL 0 13/08/2012 NULL 0 13/08/2012 NULL 0 13/08/2012 NULL 1 10/08/2012 2012-10-08 1 07/08/2012 2012-07-08 1 07/08/2012 2012-07-08
That’s odd, until you think about it at least. some of those dates are not recognised as being valid dates by the IsDate function even though we can plainly see that they are. Basically SQL Server defaults to an American date format. Now lets let that sink in… It kind of doesn’t explain anything does it! What is in fact happening is that the isDate is comparing using the American date format and because 13/08 in the US equates to the 8th day in the 13th month SQL Server rejects this as being an invalid date.
The question is, how do we properly check for dates given that isDate only accepts the text date and no instructions on the format itself? The answer lies in the SET statement. We thus add the following SQL before our main select statement:-
SET DATEFORMAT dmy; SELECT IsDate(TextDate)AS IsADate,TextDate AS TheDate, CASE WHEN IsDate (TextDate)=1 THEN CAST(TextDate AS DATE) ELSE NULL END AS TheDateAsADate FROM TheTableOfDates
This improves things massively…. or not.
Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.
My data is all dates (or non valid dates which will be ignored) now so I am unsure as to what is happening here although I did find a way around the issue which seems, quite frankly a little mad! Bear with…..
SET DATEFORMAT dmy; SELECT IsDate(TextDate)AS IsADate,TextDate AS TheDate, CASE WHEN IsDate (TextDate)=1 THEN CAST(CAST(TextDate AS DATETIME)AS DATE) ELSE NULL END AS TheDateAsADate FROM TheTableOfDates
IsADate TheDate TheDateAsADate 1 12/12/2012 2012-12-12 1 12/12/2013 2013-12-12 1 13.09/2015 2015-09-13 1 13/01/15 2015-01-13
I maintain that this double cast is still crazy, but it works as I believe the original statement should have so I’m not going to put up too much of a fight. And as a bonus, just look at how resilient it is to deviations in the user input too.