HHMMSS int field to human-friendly time?

Question

Post reply

HHMMSS int field to human-friendly time?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150457 More actions · Answer 1

Michael Valentine Jones (1/9/2012)

I simplified my original code to eliminate three DATEADD function calls and one NULLIF function call, so it might run a bit faster.

It seems a little slower on my instances; fastest run out of 10 was 1210ms (versus 1155ms previously).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

I'm using one of Michael's versions and I'm getting NULL values when there is a date and the time is 0.

i.e.:

last_run_date last_run_time LastRunDateTime

20081118 0 NULL

this one:



CREATE FUNCTION [dbo].[IntsToDate]

(

@Date integer,

@Time integer

)

RETURNS TABLE WITH SCHEMABINDING

AS RETURN

SELECT FullDateTime =

-- convert date

	dateadd(dd,((@Date)%100)-1,

	dateadd(mm,((@Date)/100%100)-1,

	dateadd(yy,(nullif(@Date,0)/10000)-1900,0)))+

	-- convert time

	dateadd(ss,@Time%100,

	dateadd(mi,(@Time/100)%100,

	dateadd(hh,nullif(@Time,0)/10000,0)))

;

(I'm reasonably good with T-SQL but when you start getting into this date/time and math stuff I tend to go a bit cross-eyed...)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Pam Brisjar (1/9/2012)

I'm using one of Michael's versions and I'm getting NULL values when there is a date and the time is 0.

...

That is something I fixed in the version that I posted today.

Or you could fix the version you are using by replacing this:

dateadd(hh,nullif(@Time,0)/10000,0)))

with this:



dateadd(hh,@Time/10000,0)))

Revenant SSC-Forever Points: 42467 More actions · Answer 4

!Aaron Aardvark! (1/9/2012)

Michael Valentine Jones (1/9/2012)

I simplified my original code to eliminate three DATEADD function calls and one NULLIF function call, so it might run a bit faster.

It seems a little slower on my instances; fastest run out of 10 was 1210ms (versus 1155ms previously).

Your actual mileage may vary, depending on the processor you are running on.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

!Aaron Aardvark! (1/9/2012)

Just for interest's sake, here's the in-line function written to use SQL Server 2012:

CREATE FUNCTION dbo.agent_datetime_inline ( @Date integer, @Time integer ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT DATETIMEFROMPARTS ( @Date / 10000, @Date / 100 % 100, @Date % 100, @Time / 10000, @Time / 100 % 100, @Time % 100, 0 ) AS date_time

Test results using Jeff's rig:

Michael's code: 1155ms

New function: 670ms

Now THAT would make a cool "spackle" article... you should go for it, Paul. Thanks for the "preview".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

aniruddh123 SSC Enthusiast Points: 110 More actions · Answer 6

Plenty of solutions already in place and I tried to built something from all:



select next_run_date,next_run_time,

CONVERT(CHAR(8), next_run_date, 112)+

CONVERT(datetime,STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':'),108) next_datetime

from dbo.sysjobschedules

But, every solution looks good 🙂