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

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