Geek asks and answers

Is there a function like isdate () for datetime2?

I know that there is a function ISDATEfor checking columns DATETIME, but it only works for types SMALLDATETIMEand DATETIME.

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

+6
source share
2 answers

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);

(The same results.)

Sorry, I don’t have a smart answer for you for <SQL Server 2012. You could, I think, say

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

But it seems dirty.

TRY_CONVERTMicrosoft Docs documentation Microsoft Docs documentation
TRY_PARSE

+13
source

LEFT(..., 23) , , mdy ( SQL-Server 2008). DBCC USEROPTIONS.

, (dmy), LEFT(..., 23) ( > 12). :

-- 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 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;

?

CAST(column_name AS DATETIME) LEFT(..., 23), :

-- 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;

dbfiddle.uk ( dmy)/ dbfiddle.uk ( mdy)

SQL Server 2012 TRY_PARSE/TRY_CONVERT, @Aaron Bertrand answer. CAST(... AS DATETIME), , .

0

More articles:


All Articles