Truncating date + time using DateTime2

Question

I have been using this format for years to truncate dates and times

SELECT  DATEADD(HOUR, DATEDIFF(HOUR, 0, '1980-02-05 12:45'), 0) AS Hour ,
        DATEADD(DAY, DATEDIFF(DAY, 0, '1980-02-05 12:45'), 0) AS Day ,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, '1980-02-05 12:45'), 0) AS Month ,
        DATEADD(YEAR, DATEDIFF(YEAR, 0, '1980-02-05 12:45'), 0) AS Year;

But I have a need to store very early dates like 1400-01-01 and therefore I can use DateTime2.

But how would I support the ability to still truncate like above using DateTime2?

Changing the year above to 1400 from 1980 will then result in

SELECT  DATEADD(HOUR, DATEDIFF(HOUR, 0, '1400-02-05 12:45'), 0) AS Hour ,
        DATEADD(DAY, DATEDIFF(DAY, 0, '1400-02-05 12:45'), 0) AS Day ,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, '1400-02-05 12:45'), 0) AS Month ,
        DATEADD(YEAR, DATEDIFF(YEAR, 0, '1400-02-05 12:45'), 0) AS Year;

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

SO casting to DateTime2

SELECT  DATEADD(HOUR, DATEDIFF(HOUR, 0, CAST('1400-02-05 12:45' AS DATETIME2)),0) AS Hour ,
        DATEADD(DAY, DATEDIFF(DAY, 0, CAST('1400-02-05 12:45' AS DATETIME2)),0) AS Day ,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('1400-02-05 12:45' AS DATETIME2)), 0) AS Month ,
        DATEADD(YEAR, DATEDIFF(YEAR, 0, CAST('1400-02-05 12:45' AS DATETIME2)), 0) AS Year;

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

I am assuming that the 0 is being treated as a DateTime data type and effectly casting it to DateTime.

Trying to cast the 0 to DateTime2 using CAST(0 AS DATETIME2) gives me this error

Explicit conversion from data type int to datetime2 is not allowed.

In the end I am wanting to use these as persisted columns in a table which worked fine with DateTime data types but not so easy with DateTime2

Answer 1

You should use some specific base date instead of 0. 0 can be implicitly converted into datetime type. For datetime2 such implicit conversion is not allowed. In addition, the base date should have a datetime2 type. Then DATEDIFF and DATEADD would work with datetime2 values.

Another reason for using explicit base date is that you need this base date to be the first day of the year and to have 00:00:00 time for the formula to work correctly. Implicit starting dates, like 0 converted to datetime or '' converted to datetime2 also have these properties right now, but do you really want to rely on internal details of the type implementation? It is better to spell out such things explicitly and it makes the formula easier to understand for a new person.

Besides, if you ever want to truncate to the week boundary using the same approach, then you'd have to pick a base date that is Monday (if your week starts on Monday) or Sunday (if your week starts on Sunday). The formula remains the same, but base date is important.

Example 1 - works

DECLARE @VarBase datetime2 = '2000-01-01';
DECLARE @VarValue datetime2 = '1400-02-05 12:45';

SELECT
    DATEADD(HOUR,  DATEDIFF(HOUR,  @VarBase, @VarValue), @VarBase) AS Hour,
    DATEADD(DAY,   DATEDIFF(DAY,   @VarBase, @VarValue), @VarBase) AS Day,
    DATEADD(MONTH, DATEDIFF(MONTH, @VarBase, @VarValue), @VarBase) AS Month,
    DATEADD(YEAR,  DATEDIFF(YEAR,  @VarBase, @VarValue), @VarBase) AS Year;

Example 2 - works

SELECT
    DATEADD(HOUR,  DATEDIFF(HOUR,  @VarBase, '1400-02-05 12:45'), @VarBase) AS Hour,
    DATEADD(DAY,   DATEDIFF(DAY,   @VarBase, '1400-02-05 12:45'), @VarBase) AS Day,
    DATEADD(MONTH, DATEDIFF(MONTH, @VarBase, '1400-02-05 12:45'), @VarBase) AS Month,
    DATEADD(YEAR,  DATEDIFF(YEAR,  @VarBase, '1400-02-05 12:45'), @VarBase) AS Year;

Example 3 - doesn't work

SELECT
    DATEADD(HOUR,  DATEDIFF(HOUR,  '2000-01-01', '1400-02-05 12:45'), '2000-01-01') AS Hour,
    DATEADD(DAY,   DATEDIFF(DAY,   '2000-01-01', '1400-02-05 12:45'), '2000-01-01') AS Day,
    DATEADD(MONTH, DATEDIFF(MONTH, '2000-01-01', '1400-02-05 12:45'), '2000-01-01') AS Month,
    DATEADD(YEAR,  DATEDIFF(YEAR,  '2000-01-01', '1400-02-05 12:45'), '2000-01-01') AS Year;

Adding a value to a 'datetime' column caused an overflow.

It doesn't work, because literal 2000-01-01 is converted into datetime, not datetime2.

Example 4 - works

SELECT
    DATEADD(HOUR,  DATEDIFF(HOUR,  CAST('2000-01-01' AS datetime2), '1400-02-05 12:45'), CAST('2000-01-01' AS datetime2)) AS Hour,
    DATEADD(DAY,   DATEDIFF(DAY,   CAST('2000-01-01' AS datetime2), '1400-02-05 12:45'), CAST('2000-01-01' AS datetime2)) AS Day,
    DATEADD(MONTH, DATEDIFF(MONTH, CAST('2000-01-01' AS datetime2), '1400-02-05 12:45'), CAST('2000-01-01' AS datetime2)) AS Month,
    DATEADD(YEAR,  DATEDIFF(YEAR,  CAST('2000-01-01' AS datetime2), '1400-02-05 12:45'), CAST('2000-01-01' AS datetime2)) AS Year;

Answer 2

Try to use:

DECLARE @Default DATETIME2 = CAST('' AS DATETIME2)
SELECT  DATEADD(HOUR, DATEDIFF(HOUR, @Default, CAST('1400-02-05 12:45' AS DATETIME2)), @Default) AS Hour ,
        DATEADD(DAY, DATEDIFF(DAY, @Default, CAST('1400-02-05 12:45' AS DATETIME2)), @Default) AS Day ,
        DATEADD(MONTH, DATEDIFF(MONTH, @Default, CAST('1400-02-05 12:45' AS DATETIME2)), @Default) AS Month ,
        DATEADD(YEAR, DATEDIFF(YEAR, @Default, CAST('1400-02-05 12:45' AS DATETIME2)), @Default) AS Year;