Formatting Date and Time Values Stored as an Int

Formatting Date and Time Values Stored as an Int

Before SQL Server 2008 introduced the DATE and TIME data types, it was common to store these values in a single INT column. Dates would be stored as an Integer consisting of yyyymmdd where yyyy = the four digit year, mm = the two digit month and dd = the two digit day.

Today would be 08/27/2013 or 20130827 as an integer.

Time was stored as hhmmss where hh = hours, mm = minutes and ss = seconds. Unlike the date portion the time INT value can be a small number, because it does not always start with a non-zero value. So, something starting at 00:00:01 would simply have an INT time value of 1. Here are some examples:

01:00:23 = 10023
23:21:04 = 122104
00:07:56 = 756

In SQL Server 2008, the MSDB database stores the history of job execution in the table sysjobhistory. I wanted to get a list of job steps that had failed, and the dates and times on which this occurred. They store the date in the column run_date and the time in the column run_time. These values both use the INT storage technique described above.

I wanted to write a simple query to let me look at the records in the table, and translate the dates into a more readable fashion for me. I created the two following scalar functions in my SysAdmin database so that I could use them to format the date and time. I use my SysAdmin database for utility SPs and Functions so I don’t modify the Master database, and so I know where to find them.

I wanted to be flexible on the formatting of the date, so I wrote the function allowing me to send in a format string (like we have used pretty much forever). This way, the format is not fixed, but flexible to the way I want to format it.

Here is the date function.

CREATE FUNCTION dbo.FormatIntDateWithMask_sfn
(
@intDate INT
,@Format VARCHAR(32)
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @YEAR CHAR(4) = CONVERT(CHAR(4), @IntDate / 10000)
DECLARE @Month VARCHAR(2) = CONVERT(VARCHAR(2), @IntDate % 10000 / 100)
DECLARE @Day VARCHAR(2) = CONVERT(VARCHAR(2), @IntDate % 100)
DECLARE @ShortMonthString = 'JanFebMarAprMayJunJulAugSepOctNovDec'
DECLARE @ShortMonth CHAR(3) = SUBSTRING( (3 * @Month - 2), 3)
DECLARE @LongMonthString CHAR(102) =
'January '
+ 'February '
+ 'March '
+ 'April '
+ 'May '
+ 'June '
+ 'July '
+ 'August '
+ 'September'
+ 'October '
+ 'November '
+ 'December '

return REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE (
REPLACE (
REPLACE(@FORMAT
, 'yyyy', @Year)
,'Month', @LongMonth)
,'Mon', @ShortMonth)
, 'mm', RIGHT('0' + @Month, 2))
, 'dd', RIGHT('0' + @Day, 2))
, 'yy', RIGHT(@Year, 2))
, 'm', @Month)
, 'd', @Day)
END
GO

The function parses the date into @Day, @Month and @Year variables. Then it replaces the appropriate values in the format string with the values in the variables using a series of SQL REPLACE commands. Nothing special there. Note that I resolve the longer format codes first such as “yyyy” before “yy”, “mm” before “m”, and “dd” before “d”.

The Time function uses the same methodology. In this example I simply use substrings of a left padded string value of the Time number submitted in the @IntTime variable. You could use math techniques like I did for the date. I used string manipulation functions for the time to demonstrate different techniques for resolving the contents of the INT value.

CREATE FUNCTION dbo.FormatIntTimeWithMask_sfn
(
@intTime INT
,@Format VARCHAR(32)
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @Time CHAR(6) = RIGHT('000000' + CONVERT(VARCHAR(6), @intTime), 6)
DECLARE @Hour CHAR(2) = LEFT(@Time, 2)
DECLARE @Minute CHAR(2) = SUBSTRING(@Time, 3, 2)
DECLARE @Second CHAR(2) = RIGHT(@Time, 2)

RETURN REPLACE(
REPLACE(
REPLACE (@FORMAT
, 'hh', @Hour)
, 'mm', @Minute)
, 'ss', @Second)
END
GO

Now that you have formatting functions for date and time integer values, let’s demonstrate them using an SQL command on the history of scheduled tasks in MSDB.

SELECT run_date
,sysadmin.dbo.FormatIntDateWithMask_sfn(run_date, 'm/d/yy')
,sysadmin.dbo.FormatIntDateWithMask_sfn(run_date, 'mm/dd/yyyy')
,sysadmin.dbo.FormatIntDateWithMask_sfn(run_date, 'yyyy/mm/dd')
,sysadmin.dbo.FormatIntDateWithMask_sfn(run_date, 'm.d.yyyy')
,sysadmin.dbo.FormatIntDateWithMask_sfn(run_date, 'Mon d, yyyy')
,sysadmin.dbo.FormatIntDateWithMask_sfn(run_date, 'Month d, yyyy')
,run_time
,sysadmin.dbo.FormatIntTimeWithMask_sfn(run_time, 'hh:mm:ss')
FROM MSDB.DBO.sysjobhistory WITH (NOLOCK)

Here are the results from the first row from my database.

20130803

8/3/13

08/03/2013

2013/08/03

8.3.2013

Aug 3, 2013

August 3, 2013

20000

02:00:00

Tomorrow I’ll share a tip for getting a listing of the failed jobs from your Job History in MSDB, taking advantage of these formatting functions.

Share your tips or optimizations by leaving a comment below, or drop me an Email at btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
SQL Server 2012 Transact-SQL Optimization Tips
In this article, you can find some helpful SQL Server 2012 Transact-SQL tips.

Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)