This seems to work and keep the precision as well:
SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))
The CAST
to DATETIME2(7)
converts the TIME(7)
value (@T
) to a DATETIME2
where the date part is '1900-01-01'
, which is the default value of date and datetime types (see datetime2
and the comment* at CAST
and CONVERT
page at MSDN.)
* ... When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.
The DATEADD()
and DATEDIFF()
function take care of the rest, i.e. adding the difference in days between the 1900-01-01
and the DATE
value (@D
).
Test at: SQL-Fiddle
As noticed by @Quandary, the above expression is considered not-determninistic by SQL Server. If we want a deterministic expression, say because it is to be used for a PERSISTED
column, the '19000101'
** needs to be replaced by 0
or CONVERT(DATE, '19000101', 112)
:
CREATE TABLE date_time
( d DATE NOT NULL,
t TIME(7) NOT NULL,
dt AS DATEADD(day,
DATEDIFF(day, CONVERT(DATE, '19000101', 112), d),
CAST(t AS DATETIME2(7))
) PERSISTED
) ;
**: DATEDIFF(day, '19000101', d)
isn't deterministic as it does an implicit conversion of the string to DATETIME
and conversions from strings to datetime are deterministic only when specific styles are used.
I like the JOIN syntax, it's clearer to me than subqueries:
UPDATE a LEFT JOIN b ON (a.ib=b.i)
SET i1 = j1,
i2 = if(isnull(b.i), j2 ,i2)
WHERE a.id = jid;
This will change the jid record: i1 to j1 always and i2 to j2 if no match exists, and to the same value if the match exists. You may want to index b.i (probably as a primary key) if there are a lot of records and you want performance.
Best Answer
Well, a couple things.
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.
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.