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

Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers, e.g. [`DateFromParts`](https://msdn.microsoft.com/en-us/library/hh213228.aspx). — HABO, Nov 30 '16 at 01:27

Vladimir Baranov · Answer 1 · 2016-11-30T00:51:47.400

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;

Thanks - but I have a problem now about it not being deterministic. See my comment on @MegaTron thread — Chris Crowe, Nov 30 '16 at 01:44
@ChrisCrowe, I'm glad you found yourself that using `CONVERT` instead of `CAST` solves the problem. — Vladimir Baranov, Nov 30 '16 at 02:55

Roman Marusyk · Answer 2 · 2016-11-30T00:08:12.397

1

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;

edited Nov 30 '16 at 00:08

answered Nov 30 '16 at 00:02

Roman Marusyk

23,328
24
73
116

Thanks for your help but I now have a problem with these formulas are non deterministic and can't be persisted to the database. ALTER TABLE dbo.QuakeRawJSON ADD [Date] AS (dateadd(day,datediff(day,CAST('' AS DATETIME2),origintime),CAST('' AS DATETIME2))) PERSISTED Msg 4936, Level 16, State 1, Line 1 Computed column 'Date' in table 'QuakeRawJSON' cannot be persisted because the column is non-deterministic. – Chris Crowe Nov 30 '16 at 01:39

score 1 · Answer 3 · edited May 23 '17 at 12:30

1

I found that instead of using CAST ('DateTimeData' as DateTime2) if I use Convert it can be a deterministic result.

As documented on this post: Cannot persist computed column - not deterministic

ALTER TABLE dbo.QuakeRawJSON
ADD [Date]  AS (DATEADD(DAY,DATEDIFF(DAY, CONVERT(DATETIME2,'',112) 
                ,origintime),CONVERT(DATETIME2,'',112))) PERSISTED;

Thanks for your help.

edited May 23 '17 at 12:30

Community

1
1

answered Nov 30 '16 at 01:51

Chris Crowe

333
1
9

Truncating date + time using DateTime2

3 Answers3