Carl Nolan’s ramblings on development
With the addition of the SQL Server date and time types, I have often found myself needing to create a datetime (or datetime2) value based on the addition of a date and a time value. However, there is no built-in function for such an operation.
There are a few solutions to this problem that encompass processing strings; such as:
However, if one realizes that when a time value is cast to a datetime the date component is set to '1900-01-01', and this date equates to a days equivalent of zero in the SQL date and time functions. Thus the following user-defined function can be used to add a date and time value:
This works as the DATEDIFF function with a zero value and a date returns the number of days from the date '1900-01-01'. Thus adding the DATEDIFF result to a time value cast to a datetime value, effectively adds the date and time values.
Thus one can now write:
In the same manner if one wanted to extract just the date portion of a datetime variable, one option is to perform a cast to and from a date type. However one can also start from day 0 and add the corresponding number of days:
cast(begin_date as datetime) + cast(begin_time as datetime)
or just add a string to a datetime:
declare @date as datetime = '2013-08-09 00:00:00.000'
declare @time as nvarchar(8) = '15:33:26'
select @date, @time, @date+@time
(No column name) (No column name) (No column name)
2013-08-09 00:00:00.000 15:33:26 2013-08-09 15:33:26.000