← Back to postsSQL - Unusual DateTime Functions in Computed Columns

SQL - Unusual DateTime Functions in Computed Columns

Published: 3/11/2017

I've had the pleasure of working with a data repository that stored date and time values as string data. Without getting into weeds of that, I'll just say I wasn't able to change anything about the repository, and therefore, had to work with it as is.

When I say that date and time values were stored as strings, this is what I mean. The date/time April 25, 2018 at 10:28am would have been stored in two separate fields as strings:

  • a Date field with the contents "20180425" indicating yyyymmdd
  • a Time field with the contents "1028" indicating hhmm.

Fortunately, I was able to used Stored Procedures to copy/move the data into a target database, where I did a good bit of manipulation using Computed Fields. Here are some of the fun things I did with Computed Fields.

  1. Turn the Time string representing hhmm (an event in time) into minutes from midnight. In order to do this, I had to select the hour portion of a string called Admittime, turn it numeric, and multiply it by 60, then turn the minute portion numeric, and add it to the hours. This returned a number of minutes from midnight that a particular event occurred.
    (CONVERT([numeric](10,0),substring([Admittime],(1),(2))*(60))+CONVERT([numeric](10,0),substring([Admittime],(3),(4))))
  2. Calculate number of minutes from midnight it is "right now." I used the datepart method to select portions of the datetime value from getdate().
    (datepart(hour,getdate())*(60)+datepart(minute,getdate()))
  3. Compare the values of item # 1 and 2, but do it all in one field. You can't create a computed field from another computed field. So, in order to compare the values from 1 and 2, I had to do it all in one field.
    ((datepart(hour,getdate())*(60)+datepart(minute,getdate())) - (CONVERT([numeric](10,0),substring([Admittime],(1),(2))*(60))+CONVERT([numeric](10,0),substring([Admittime],(3),(4)))))


This is just one way to handle a string date/time, and it worked fine... until I had to calculate across a midnight divide. The problem with my original calculation in items 1 and 3 is that it always assumes the event date = today, because it's computing minutes from today's midnight. If that's true, the calculation works. If it's not true, the calculation is broken.

Instead of doing a bunch of manipulation in the target database after the data was already populated, I decided to use one of my stored procedures to convert the date/time strings and populate a proper Datetime field.

First I created a Datetime field called AdmitDateTime in the target database. In this case, I did not set a default value. Then in my stored procedure, I did the following:
UPDATE tablename
SET AdmitDateTime = (substring(AdmitDate,1,4) + '-' + substring(AdmitDate,5,2) + '-' + substring(AdmitDate,7,2) + ' ' + substring(Admittime, 1, 2) + ':' + substring(Admittime, 3, 2))
WHERE AdmitDateTime is null

This resulted in a Datetime field that I could then do typical date/time calculations against. I still used a Computed Field to figure out the number of minutes between an event and "now," but the calculation was much simpler:
(datediff(minute,[AdmitDateTime],getdate()))