Serial Date conversion

Question

Serial Date conversion

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
November 4, 2014 at 8:12 am

#312684

Good morning just has a small issue, I am in need of converting serial date to regular date ie...735510.40461 and only need the hours, minutes and seconds, I have used the examples I've seen on different forums,. Any good ways of doing this, I am using this to determine time to answer calls.
DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))

Viewing 13 posts - 1 through 12 (of 12 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Based on the value 735510.40461, what are you expecting to get?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 2

I am subtracting Pending time 735510.30987 from Delivered time 735510.40448 and get call duration 0.09474, but I want to convert this to standard time.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

cbrammer1219 (11/4/2014)
I am subtracting Pending time 735510.30987 from Delivered time 735510.40448 and get call duration 0.09474, but I want to convert this to standard time.

And what do those values mean? Numeric representation of dates isn't a standard (not even between MS products).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Quick thought, is it this you are looking for?

😎

SELECT DATEADD(HOUR,735510.40461,'1900-01-01 00:00:00.000')

Result

1983-11-28 06:00:00.000

The time element

SELECT CONVERT(TIME,DATEADD(HOUR,735510.40461,'1900-01-01 00:00:00.000'),0)

Result

06:00:00.0000000

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Erikur, I don't know how you got that bit of logic from the decimal values, but well done! It seems to make sense. I was hoping for more detail, but you seem to have found the head of the nail in the dark.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

Ed Wagner (11/4/2014)
Erikur, I don't know how you got that bit of logic from the decimal values, but well done! It seems to make sense. I was hoping for more detail, but you seem to have found the head of the nail in the dark.

Kind of obvious, when I woke up this morning it was 1006684 hours since 1900-01-01 00:00:00;-)

😎

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 7

This is what I have done, then I take that and do a DATEDIFF on the 2 values to get the seconds.

CONVERT(varchar(8),CONVERT(datetime,case

when alhist.[TIME_OF_FIRST_CALL] = '' '' then 0.0

when alhist.[TIME_OF_FIRST_CALL] = '''' then 0.0 else CAST(rtrim(ltrim(alhist.[TIME_OF_FIRST_CALL])) AS DECIMAL(12,5)) - 693596 end),108) as [TIME_OF_FIRST_CALL]

DATEDIFF(ss,CONVERT(datetime,hist.[NOPR_DELIVERED_TIME]) ,CONVERT(datetime,hist.[NTIME_OF_COMPLETION])) calls_in_seconds

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 8

Ok so no that I have that, I am looking to do a case so that if the value can be converted to minutes, hours or leave as seconds. Not real sure how to implement this. Any Ideas??? Here is some of the values...These values are in seconds.

CALL_DURATION_IN_SECS

36

2110

60

10

41809

4

1357

3660

4

5

1892

9

2

34069

22

32

9

4055

326

5618

59

1412

8

2

74

aaron.reese SSChampion Points: 13498 More actions · Answer 9

60 seconds in a minute,

3600 seconds in an hour.

You will be looking for the FLOOR() function and the MOD operator

FLOOR(200 / 60) = 3

200 % 60 = 20

200 Seconds = 3m20s

If you want to do calculations with them I suggest keeping them in seconds though. Convert them to HH:MM:SS strings in your reporting tool of choice

ALSO DON'T FORGET!!!!!!!!!

Integers divided by integers = integers (i.e. 10/3 = 3 NOT 3.33333...) If you need decimal precision you will need to CAST() the values to decimal(m.n)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

cbrammer1219 (11/4/2014)
Ok so no that I have that, I am looking to do a case so that if the value can be converted to minutes, hours or leave as seconds. Not real sure how to implement this. Any Ideas??? Here is some of the values...These values are in seconds.
CALL_DURATION_IN_SECS
36
2110
60
10
41809
4
1357
3660
4
5
1892
9
2
34069
22
32
9
4055
326
5618
59
1412
8
2
74

Yes... what format do you want them to be displayed in?

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

cbrammer1219 (11/4/2014)
This is what I have done, then I take that and do a DATEDIFF on the 2 values to get the seconds.
CONVERT(varchar(8),CONVERT(datetime,case
when alhist.[TIME_OF_FIRST_CALL] = '' '' then 0.0
when alhist.[TIME_OF_FIRST_CALL] = '''' then 0.0 else CAST(rtrim(ltrim(alhist.[TIME_OF_FIRST_CALL])) AS DECIMAL(12,5)) - 693596 end),108) as [TIME_OF_FIRST_CALL]
DATEDIFF(ss,CONVERT(datetime,hist.[NOPR_DELIVERED_TIME]) ,CONVERT(datetime,hist.[NTIME_OF_COMPLETION])) calls_in_seconds

Heh... you change table and column names faster than anyone can keep up. 😉 With that in mind, here's a generic solution with "StartTime" and "EndTime". Change the column names to suit your needs.

First, we'll build my typical million row test table using hour serial numbers like in your original post. Note that I didn't limit this to 24 hours of duration. Also notice that it doesn't take long to build (usually, less than 2 seconds).

--===== Build and populate test table on-the-fly.

WITH

cteBuildSerials AS

(

SELECT TOP 1000000

StartTime = RAND(CHECKSUM(NEWID()))*1000+735510

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

)

SELECT StartTime = CONVERT(DECIMAL(12,5),StartTime)

,EndTime = CONVERT(DECIMAL(12,5),StartTime + RAND(CHECKSUM(NEWID()))*100)

INTO #TestTable

FROM cteBuildSerials

;

Then, if you want the duration in a typical HHHHHH:MM:SS format, this is how you could pull that off.

SELECT StartTime

,EndTime

,Duration = CONVERT(VARCHAR(10),DATEDIFF(hh,0,Duration))

+ RIGHT(CONVERT(CHAR(8),Duration,108),6)

FROM #TestTable tt

OUTER APPLY (SELECT DATEADD(ss,(EndTime-StartTime)*3600,0)) oa (Duration)

;

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

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 12

Use both methodsto get hole days plus formatted HH:MM:SS

select

CALL_DURATION_IN_SECS

, days = CALL_DURATION_IN_SECS/86400

, time = cast (dateadd(second,CALL_DURATION_IN_SECS,'00:00') as time(0))

from (values

(36),

(2110),

(60),

(10),

(41809),

(24*60*60+1),

(1357),

(3660),

(4),

(5),

(1892),

(9),

(2),

(34069),

(22),

(32),

(9),

(4055),

(326),

(5618),

(59),

(1412),

(8),

(2),

(74)

) t (CALL_DURATION_IN_SECS)