|
Splitting and joining date and time values (Development | SQL)
Today I’ve been evaluating database designs made by this year’s candidates for work at our company. One of the issues I’ve been encountering all the time was the handling of date and time values. The SQL Server’s common data type datetime for both values has been the source of quite some confusion, especially since the values required to be separate at some occasions and joined at others.
I suppose the decision on whether to store them separate or joined depends on how the values are going to be used. In most cases the extra storage space required by using separate columns for both values isn’t worth it but in some usage scenarios the resulting reduced query complexity and improved indexing usage can justify it.
Either way sooner or later the need for separating or joining the date and time parts will arise. The following query demonstrates both operations in what I believe is the most efficient way:
SELECT
dateOnly = CONVERT(datetime, FLOOR(CONVERT(float, dateTimeColumn))),
timeOnly = dateTimeColumn - CONVERT(datetime, FLOOR(CONVERT(float, dateTimeColumn))),
dateAndTime = dateOnlyColumn + timeOnlyColumn
FROM DateTable
4/18/2006 10:38:07 PM (Central Europe Standard Time, UTC+01:00) 
|