10

I know there is a function called ISDATE to validate DATETIME columns, but it works only for the SMALLDATETIME and DATETIME types.

Is there a similar way to validate the new data type DATETIME2 in SQL Server 2008 and 2012?

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
Vladimir
  • 196
  • 3
  • 3
  • 12
  • 3
    strictly speaking ISDATE() does not validate datetime columns; it validates whether an expression can be parsed as a datetime. After it's in a column it's a bit late! – Mitch Wheat Jun 15 '12 at 02:46
  • 1
    @MitchWheat that's a good point. Seems more likely to validate either (a) dates stored incorrectly in a varchar column or (b) input parameters that are of an incorrect data type. – Aaron Bertrand Jun 15 '12 at 02:54
  • I'd like to validate input from an excel sheet that has some date columns into a temporary table. – Vladimir Jun 22 '12 at 00:44

2 Answers2

19

In SQL Server 2012, you can use TRY_CONVERT:

SELECT TRY_CONVERT(DATETIME2, '2012-02-02 13:42:55.2323623'),
       TRY_CONVERT(DATETIME2, '2012-02-31 13:42:55.2323623');

Results:

2012-02-02 13:42:55.2323623    NULL

Or TRY_PARSE:

SELECT TRY_PARSE('2012-02-02 13:42:55.2323623' AS DATETIME2),
       TRY_PARSE('2012-02-31 13:42:55.2323623' AS DATETIME2);

(Same results.)

Sorry that I don't have a clever answer for you for < SQL Server 2012. You could, I guess, say

SELECT ISDATE(LEFT('2012-02-02 13:42:55.2323623', 23));

But that feels dirty.

TRY_CONVERT documentation on Microsoft Docs
TRY_PARSE documentation on Microsoft Docs

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Clever answer! I liked it very much! Another question about the datetime2 in sql server 2008, is there a way to check for valid date when the value is something like before "January 1, 1753"? Using this command would yeld invalid date SELECT ISDATE(LEFT('2012-02-02 13:42:55.2323623', 23)); – Vladimir Jun 16 '12 at 05:39
  • @Vladimir sorry I just noticed your question now. Is it academic or do you really have dates < 1753? – Aaron Bertrand Oct 04 '12 at 14:37
  • It's academic. I was wondering how could I validate datetime input when inserting date values into a datetime2 column with something like the isdate() function that works only for the datetime format. The try_contert ant the try_parse functions work only on SQL Server 2012. – Vladimir Oct 05 '12 at 18:53
  • 1
    @Vladimir in cases where left(4) < 1754 you could convert to a string, take left 4 as an int, add (19-left(2))*100, then reassemble the string, and that would put your date in the 1900s. You still have some dates to worry about (e.g. September 1753), and leap years won't necessarily align depending on the starting year, but you'd cover most of your bases. – Aaron Bertrand Oct 05 '12 at 18:59
0

Be careful using the LEFT(..., 23) solution on database systems using another dateformat than mdy (and SQL-Server 2008). You can see the dateformat of the current session using the DBCC USEROPTIONS command.

On a database system using the german dateformat (dmy) the LEFT(..., 23) solution isn't working (detected on dates with day > 12). See the following test case:

-- test table using a DATETIME and DATETIME2 column.
CREATE TABLE dt_vs_dt2 (
  dt DATETIME,
  dt2 DATETIME2
);

-- set a datetime values with a day > 12.
DECLARE @date_value AS DATETIME = DATEADD(DAY, 18 - DAY(GETDATE()), GETDATE());

-- insert the current date into both columns using GETDATE.
-- note: using the following on a day > 12
INSERT INTO dt_vs_dt2 VALUES (@date_value, @date_value);

-- let's have a look at the values.
-- the values look the same (the datetime2 is more precise as expected).
SELECT dt, dt2 FROM dt_vs_dt2;

-- now we expect both values are valid date values.
-- to validate the datetime2 value, the LEFT(..., 23) solution is used.
SELECT ISDATE(dt), ISDATE(LEFT(dt2, 23)) 
FROM dt_vs_dt2;

How to solve that?

You can use a CAST(column_name AS DATETIME) instead of the LEFT(..., 23) to make this work:

-- using a CAST(... AS DATETIME) instead of `LEFT(..., 23)` seems to work.
SELECT dt, CAST(dt2 AS DATETIME) AS dt2
FROM dt_vs_dt2;

-- now both values are valid dates.
SELECT ISDATE(dt) AS dt, ISDATE(CAST(dt2 AS DATETIME)) AS dt2
FROM dt_vs_dt2;

demo on dbfiddle.uk (using dmy) / demo on dbfiddle.uk (using mdy)

On SQL Server 2012 and later you should use the TRY_PARSE / TRY_CONVERT solution described in @Aaron Bertrand answer. The CAST(... AS DATETIME) solution explained in this answer should also work.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87