How to convert '01/01/0001' to datetime2

Question

I was trying to display a '01/01/0001' from sql if the value is null. But when I convert the value into datetime2, it says:

Msg 517, Level 16, State 3, Line 18 Adding a value to a 'datetime2' column caused an overflow.

this is what I tried:

    Declare @NewDate as DateTime2


        SELECT  @Tem = Ab.Date_out

        FROM    Timesheet_header Ab
        WHERE (Ab.Employee_id = 3424)
            AND (Ab.Work_date BETWEEN ('2017-08-31') AND ('2017-08-31'))
            print @Tem

        if(@Tem IS NULL)
            Begin
                set @NewDate = CONVERT(VARCHAR, DATEADD(DAY,-5,CAST('0001-01-01' AS DATETIME2)),100)
--some select clause here
            End
        else
            Begin
                --some select clause here
            End

            print @Tem
            print @NewDate

Answer 1

Here is your problem:

DATEADD(DAY,-5,CAST('0001-01-01' AS DATETIME2))

Datetime2 min value is 0001-01-01, but you try to create a value that's 5 days before.

From MSDN:

Date range 0001-01-01 through 9999-12-31

Also, your @NewDate variable is of type datetime2, but you try to populate it with varchar(100) -

    set @NewDate = CONVERT(VARCHAR, DATEADD(DAY,-5,CAST('0001-01-01' AS DATETIME2)),100)

That makes no sense. If anything, you should be doing something simple like this:

SET @NewDate = '0001-01-01';