Constructing a datetime from an integer date and an integer time.

3

August 15, 2019 by Kenneth Fisher

Occasionally I’ve seen date and time stored separately as integers. This had some practical applications back before we had date and time data types but there’s still lots of legacy code out there that use them (I’ll give you a really really common example in just a minute).

Unfortunately, you can’t convert datetime to date and time ints directly but it isn’t all that difficult.

SELECT CAST(CONVERT(char(8),@MyDatetime,112) AS int) AS IntDate,
	CAST(REPLACE(CONVERT(char(8),@MyDatetime, 108), ':', '') AS int) AS IntTime;

Going the other direction gets more complicated. You can completely reverse the date pretty simply.

DECLARE @MyIntDate int = 20190803;
SELECT CAST(CONVERT(char(8), @MyIntDate) as date);

The time is where it gets hard because midnight is 0, one minute after is 1, 1am is 100 etc. I mean you could convert the int to varchar, add any needed 0s to the left then add in :s to the right places and then finally convert it to time.

DECLARE @MyIntTime int = 1;
SELECT CAST(STUFF(STUFF(RIGHT('000000'+CAST(@MyIntTime AS varchar(20)),6),5,0,':'),3,0,':') AS time);

And then you combine all of that together to get a datetime. Or you can use a handy dandy, Microsoft provided, function.

DECLARE @MyIntDate int = 20190803;
DECLARE @MyIntTime int = 1;
SELECT msdb.dbo.agent_datetime(@MyIntDate, @MyIntTime);

Kind of odd that there would be a function in msdb to do this right? Well, remember at the beginning of this post where I said there was some common legacy code that uses int dates and int times? msdb.dbo.sysjobhistory stores the job run dates and times as integers.

FYI the run duration is also stored as an integer, and I’m afraid I haven’t found a convenient function for that so you’ll have to use something similar to the command above. It’s in the format HHMMSS so for durations longer than 99 hours (yea it can happen) then it’s actually HHHMMSS so you have to add an extra 0 and shift everything right one spot (let’s really hope you don’t have a job running 1000+ hours). Which also means you can’t convert it to time.

DECLARE @MyIntTime int = 1;
SELECT STUFF(STUFF(RIGHT('0000000'+CAST(@MyIntTime AS varchar(20)),7),6,0,':'),4,0,':');

3 thoughts on “Constructing a datetime from an integer date and an integer time.

  1. dallasbikr says:

    — A thing of beauty
    SELECT
    j.[name],
    msdb.dbo.agent_datetime(run_date, run_time) as [Job Run Time]
    FROM
    msdb..sysjobs j
    JOIN msdb..sysjobhistory jh
    ON j.job_id = jh.job_id
    WHERE 1=1
    ORDER BY
    msdb.dbo.agent_datetime(run_date, run_time) DESC;

  2. Mark says:

    Just change the duration to seconds with oldskool math: Then you can dateadd that to your SWEEEET trick to get the start date when you want a finish date/time.

    select *
    ,msdb.dbo.agent_datetime(run_date, run_time) AS Start
    ,DATEADD(second,run_duration/10000 *3600+ run_duration/100 % 100 * 60 +run_duration % 100,msdb.dbo.agent_datetime(run_date, run_time)) AS Finish
    from msdb..sysjobhistory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,528 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: