convert int to datetime then round

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

convert int to datetime then round

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-19 : 19:28:27

I'd like to round to the nearest minute. Our database stores
time as seconds (datatype INT).

I use this conversion to get the seconds into hh:mm:ss format:

select convert(char(8), dateadd(second, arrivetime, ''), 114)

eg
SELECT ARRIVETIME, CONVERT(CHAR(8), DATEADD(SECOND, ARRIVETIME, ''), 114) AS 'HH:MM:SS'
FROM TABLE

RESULTS:
22242 , 06:10:42

I understand that if the arrivetime column was in a datetime datatype, I could use:

ROUND(time_column, 'MI') to accomplish the rounding.

If I plug in the 'HH:MM:SS' expression above into the rounding conversion:

ROUND(CAST(CONVERT(CHAR(8), DATEADD(SECOND, ARRIVETIME, ''), 114) AS DATETIME), 'MI')

it returns the error:
"Argument datatype varchar is invalid for agrument 2 of round function"

Is there another way to do this conversion?

My limitations are that I can not declare a scalar variable/cursor etc and I can not use UDF's. I can use CTE's though.

Thanks in advance for your help!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-19 : 23:20:30

UPDATE:

Ok, this isn't exactly what I was expecting to do, but this looks like it's going to work. I was hoping for a shorter solution as this is going to get plugged into a much larger query, but it's giving me the same results I need:

SELECT ARRIVE_HHMM, DEPART_HHMM, DATEDIFF(MI, ARRIVE_HHMM, DEPART_HHMM) AS TEST, DEPART-ARRIVE AS RESULT, (DEPART-ARRIVE)*1.0/3600 AS TEST
FROM
(
SELECT ARRIVE_HH+':'+ARRIVE_MM AS ARRIVE_HHMM, DEPART_HH+':'+DEPART_MM AS DEPART_HHMM,
(ARRIVE_HH*3600)+(ARRIVE_MM*60) AS ARRIVE, (DEPART_HH*3600)+(DEPART_MM*60) AS DEPART

FROM
(
SELECT ARRIVE_HH, DEPART_HH,
CASE WHEN LEN(ROUND_A_MM)=1 THEN '0'+CONVERT(VARCHAR, ROUND_A_MM) ELSE CONVERT(VARCHAR, ROUND_A_MM) END AS ARRIVE_MM,
CASE WHEN LEN(ROUND_D_MM)=1 THEN '0'+CONVERT(VARCHAR, ROUND_D_MM) ELSE CONVERT(VARCHAR, ROUND_D_MM) END AS DEPART_MM
FROM
(SELECT CONVERT(VARCHAR, ACTUALARRIVETIME/3600) AS ARRIVE_HH,
CONVERT(VARCHAR, ACTUALDEPARTTIME/3600) AS DEPART_HH,
CASE WHEN ACTUALARRIVETIME%60>=30 THEN ACTUALARRIVETIME%3600/60+1 ELSE ACTUALARRIVETIME%3600/60 END AS ROUND_A_MM,
CASE WHEN ACTUALDEPARTTIME%60>=30 THEN ACTUALDEPARTTIME%3600/60+1 ELSE ACTUALDEPARTTIME%3600/60 END AS ROUND_D_MM
FROM EVENTS
WHERE SCHID IN (SELECT SCHID FROM SCHEDULES WHERE LDATE=[DATE,Date])
AND ACTUALARRIVETIME>0
AND ACTUALDEPARTTIME>0
) A
) B
) C

If someone has a less clumsy way to run this, I'm still open..thanks for looking!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-20 : 00:43:45

SELECT DateAdd(minute, DateDiff(minute, 0, ArriveTime), 0)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-20 : 03:18:26

Hmmm...doesn't seem to be rounding...but I'll keep playing with it...the query I wrote is working great, but I'd imagine it will
be very slow for wide date ranges.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-20 : 13:41:08

SELECT CONVERT(CHAR(10), DATEADD(MINUTE, ROUND(22242 / 60.0, 0), 0), 108)

N 56°04'39.26"
E 12°55'05.63"

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-25 : 13:33:44

Peso,
My solution worked, but that's great! It is much less cumbersome and probably a lot more efficient. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources