Numeric Representation of Date fields

[intapiuser]

Business Case

Creating a “Date” table which is identified by a numeric representation of a Date field instead of the Date field itself may improve the query performances. In addition, a numeric representation of a Date can provide us later on with the ability of filtering the data by time range.

Solution

In order to convert a Date (“4/21/2012 12:36:56 AM”) to an integer (20120421) we could use the following syntax:

10000*getyear(Date)+100*getmonth(Date)+getday(Date)

In any case we would like to get the hours and minutes too, we would need to use a float numeric representation, adding the minutes and seconds AFTER the dot. So, "4/21/2012 12:36:56 AM" would become: 2012042100.3656, using the following syntax:

getyear(DateTime)*1000000+
getmonth(DateTime)*10000+
getday(DateTime)*100+
gethour(DateTime)+
ToDouble(GetMinute(DateTime))/100+
ToDouble(GetSecond(DateTime))/10000