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.
Since Char 12 and 13 seem to be hours, with SQL Server 2012, you can build a new time using this part and 0 for missing parts:
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0)
MSDN: TIMEFROMPARTS (Transact-SQL)
With SQL Server prior to 2012, you can use such queries:
Select DATEADD(HOUR, Cast(SUBSTRING(@x, 12, 13) as int), CAST('00:00' as time))
Select Cast(SUBSTRING(@x, 12, 13)+':00' as time)
Best Answer
There is no reason, that I know of to use
uuid_generate_v1()overuuid_generate_v4(). That said, Philᵀᴹ is largely correct when he saysEven if your display time is set to DST, (or something like CSTCDT), the underlying storage mechanism should be UTC.
That said, though everything should be stored as UTC, it's not always that way. There are exceptions, like with XP, or Windows 7, you may see some kind of abnormality, especially if you permit the other OS to modify the RTC.
And, there is always the problem of leap seconds if you really want to nitpick, when some kernels or daemons will roll back the second and it's basically up to them to see if they support this. Linux-proper and OSX does, Android does not afaik humorous NDT on Leap seconds
For reference, the spec details the process of generating a UUID v1 time-based UUID, 4.2 Algorithms for Creating a Time-Based UUID.