SQL Server 2012

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 dmy;
-- 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:16:50
18/08/2019 09:16:50


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


-- 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:16:50
18/08/2019 09:16:50


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