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: