November 4, 2014 at 8:12 am
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'))
November 4, 2014 at 8:55 am
Based on the value 735510.40461, what are you expecting to get?
November 4, 2014 at 9:02 am
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.
November 4, 2014 at 9:09 am
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).
November 4, 2014 at 10:12 am
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
November 4, 2014 at 10:19 am
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.
November 4, 2014 at 10:26 am
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;-)
😎
November 4, 2014 at 1:55 pm
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
November 4, 2014 at 2:31 pm
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
November 5, 2014 at 3:28 am
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)
November 5, 2014 at 7:07 pm
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
November 5, 2014 at 8:19 pm
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
November 6, 2014 at 3:20 am
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)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply