By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- on a german system this is the default value.
SET DATEFORMAT mdy;
-- 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);
1 rows affected
-- 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;
| dt | dt2 |
|---|---|
| 18/08/2019 09:17:24 | 18/08/2019 09:17:24 |
-- now we expect both values are valid date values.
-- to validate the datetime2 value, the LEFT(..., 23) solution is used.
SELECT ISDATE(dt) AS dt, ISDATE(LEFT(dt2, 23)) AS dt2
FROM dt_vs_dt2;
| dt | dt2 |
|---|---|
| 1 | 1 |
-- using a CAST(... AS DATETIME) instead of `LEFT(..., 23)` seems to work.
SELECT dt, CAST(dt2 AS DATETIME) AS dt2
FROM dt_vs_dt2;
| dt | dt2 |
|---|---|
| 18/08/2019 09:17:24 | 18/08/2019 09:17:24 |
-- now both values are valid dates.
SELECT ISDATE(dt) AS dt, ISDATE(CAST(dt2 AS DATETIME)) AS dt2
FROM dt_vs_dt2;
| dt | dt2 |
|---|---|
| 1 | 1 |