I am tireless with my writing of short articles or tidbits on Transaction SQL date/time functions.
Most, if not all, modern day computer systems collect data which contains dates and times.
Sometimes data comes from external sources in a textual format. How do we know if the data is a valid date/time variable?
Today, I will be exploring the ISDATE() function that takes a character string as input and returns an integer as output. A numeric 1 represents a valid date and a numeric 0 represents an invalid date.
The examples below are sample calls to the function. The inputs puts are valid strings, invalid strings, a null value and a empty string.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
/* Is my string a valid date or time? */ -- Valid date/time strings SELECT 'VALID DATE TIME' AS HEADER, ISDATE('2013-12-10 12:32:10.250') AS FLAG UNION ALL SELECT 'VALID DATE' AS HEADER, ISDATE('2013-12-10') AS FLAG UNION ALL SELECT 'VALID TIME' AS HEADER, ISDATE('12:32:10.250') AS FLAG UNION ALL -- Missing or empty strings SELECT 'NULL VALUE' AS HEADER, ISDATE(NULL) AS FLAG UNION ALL SELECT 'EMPTY STRING' AS HEADER, ISDATE('') AS FLAG UNION ALL -- Invalid date/time strings SELECT 'INVALID DATE TIME' AS HEADER, ISDATE('2013-42-10 12:70:10.250') AS FLAG UNION ALL SELECT 'INVALID DATE' AS HEADER, ISDATE('2013-12-52') AS FLAG UNION ALL SELECT 'INVALID TIME' AS HEADER, ISDATE('42:32:10.250') AS FLAG; GO |
The output from the test is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
output: HEADER FLAG ----------------- ----------- VALID DATE TIME 1 VALID DATE 1 VALID TIME 1 NULL VALUE 0 EMPTY STRING 0 INVALID DATE TIME 0 INVALID DATE 0 INVALID TIME 0 |
In summary, the ISDATE() function is a quick way to determine if a string can be converted to a date/time variable. Next time, I will be talking about the end of month function EOMONTH() which was introduced to TSQL in 2012.