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


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,':');

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

  1. […] über Constructing a datetime from an integer date and an integer time. […]

  2. dallasbikr says:

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

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

  4. […] Kenneth Fisher goes over one of the things in SQL Agent which make me shudder: […]

  5. Cleomari Mendez says:

    Hi. I’m building a SQL query where shows the monthly production in a certain year. The user has to ingress the year (defined as an integer variable), for instance= 2020, to get a monthly report of this year required. How can I change the year variable from an integer to datetime? Internally, my query uses tables with datetime format.

Leave a Reply

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

You are commenting using your 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 3,544 other followers

Follow me on Twitter

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