HHMMSS int field to human-friendly time?

Question

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: 150467 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_datelast_run_timeLastRunDateTime

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: 1004324 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)

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 🙂