Splitting and Joining Date and Time Values

April 18th 2006 Microsoft SQL Server

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. 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

Get notified when a new blog post is published (usually every Friday):

If you're looking for online one-on-one mentorship on a related topic, you can find me on Codementor.
If you need a team of experienced software engineers to help you with a project, contact us at Razum.
Copyright
Creative Commons License