0

I want to create a SQL Script to clean datetime2 field and set it up to datetime2 begin '1-1-0001 12:00:00'. How to set GETDATE() to '1-1-0001 12:00:00' instead of using convert function ?

UPDATE XYZTable
SET ReferralDate = CONVERT(datetime2, '1/1/0001 09:00')
Improve this question
4
  • getdate() = '01-01-0001 12:00:00' or set ReferralDate = '01-01-0001 09:00:00'?
    – McNets
    Mar 21, 2017 at 18:49
  • 1
    Are you using "the beginning of time" to signify "date unknown"? Why? Don't fall into the "NULLs are evil and must be avoided at all costs because performance and what Date and Codd said decades ago" etc. etc. If you don't know the date, store NULL. You're going to have to special case your queries anyway, and NULL takes up less space.
    – Aaron Bertrand
    Mar 21, 2017 at 19:29
  • I agree with @AaronBertrand, unless of course NULL means something different than your canary value.
    – Erik Darling
    Mar 21, 2017 at 19:47
  • We are using '01-01-0001 12:00:00' as the default value for our applications, but I need to reset some records to '01-01-0001 12:00:00' to fix some issue with the reports.
    – NinjaDeveloper
    Mar 21, 2017 at 20:11

1 Answer 1

Reset to default
4

Well, a couple things.

  1. GETDATE() returns a datetime value, which has a valid range of January 1, 1753, through December 31, 9999. 1753 is when the civilized (or is it civilised?) world switched to the Gregorian calendar from the Julian one. So you won't be able to have it return the date you're after; it's out of range.

  2. You can use SYSDATETIME() to get you there, because it returns a datetime(2) value. However, it won't necessarily be any cleaner code-wise than using CONVERT on that string. You'd have to throw some weird date math at it, like SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, SYSDATETIME(), '00010101 12:00:00.0000000'), SYSDATETIME()).

If this is just going to be a canary value, you're better off setting it as the column default value.

ReferralDate DATETIME(2) DEFAULT '00010101 12:00:00.0000000'

If you need to update, it might be a little simpler to set it in a variable and then update using the variable.

CREATE TABLE #crap (Id INT IDENTITY(1,1), Dt DATETIME2(7))
GO 

INSERT #crap ( Dt )
    VALUES ( NULL)
GO 1000

SELECT *
FROM #crap AS c

DECLARE @dt DATETIME2(7) = '00010101 12:00:00.0000000'

SELECT @dt

UPDATE c
SET c.Dt = @dt
FROM #crap AS c
WHERE c.Dt IS NULL

SELECT * 
FROM #crap AS c
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.