Using Datediff to Calculate Hours & Minutes

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

Using Datediff to Calculate Hours & Minutes

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-02-01 : 05:32:10

Im currently using DATEDIFF to get the difference of two datetime fields in relation to hours, is it possible to to get the difference in hours and minutes?

I will need to calculate savings from this value so i will be multiplying my result by a number of fields so i guess ill need it as a decimal or something. How is this usually done, heres a little bit of my SQl this calculates the time in hours, im hoping to calculate hours and minutes


sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night

Ive tried this (code below) and it works and calculates in minutes but when i divide by 60 it then just displays the hours again i need both. my savings will be calculated on a per hour basis rate so that why i need it a way that i can calculate and multiple it out. I know theres easy ways around it like dividing my answer by 60 and that but Im just looking to see if there is a neat way of doing it.


sum(datediff(Minute, day_start, day_end)/60.0) as hours_off_day, sum(datediff(Minute, night_start, night_end)/60.0) as hours_off_night

Thanks

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 05:42:36

[code]convert(varchar(8),dateadd(mi,sum(datediff(Minute, day_start, day_end)),0),108) as hours_off_day, convert(varchar(8),dateadd(mi,sum(datediff(Minute, night_start, night_end)),0),108) as hours_off_night[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-01 : 05:44:32

[code]CREATE FUNCTION dbo.fnMyTimeFormat
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS VARCHAR(80)
AS
BEGIN
RETURN (
SELECT CASE DATEPART(DAYOFYEAR, Duration)
WHEN 2 THEN '1 Day, '
ELSE CAST(DATEDIFF(DAY, 0, Duration) AS VARCHAR(11)) + ' Days, '
END +
CASE DATEPART(HOUR, Duration)
WHEN 1 THEN '1 Hour, '
ELSE DATENAME(HOUR, Duration) + ' Hours, '
END +
CASE DATEPART(MINUTE, Duration)
WHEN 1 THEN '1 Minute, '
ELSE DATENAME(MINUTE, Duration) + ' Minutes, '
END +
CASE DATEPART(SECOND, Duration)
WHEN 1 THEN '1 Second, '
ELSE DATENAME(SECOND, Duration) + ' Seconds'
END
FROM (
SELECT DATEADD(SECOND, ABS(DATEDIFF(SECOND, @FromDate, @ToDate)), 0) AS Duration
) AS d
)
END[/code]

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

Sachin.Nand

2937 Posts

Posted - 2010-02-01 : 05:47:05

Here is my short at this one

declare @stdt as datetime='10-jan-2010 12:10:00'
declare @enddt as datetime='12-jan-2010 14:05:00'
declare @Timediff int
select @Timediff=datediff(mi,@stdt,@enddt)
select CAST(@Timediff / 1440 AS VARCHAR(12)) + ' day(s) ' + CONVERT(CHAR(8), DATEADD(MINUTE, @Timediff % 1440, '00:00'), 108)AS Timediff

PBUH

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-02-01 : 05:50:48

Visakh,

Thanks for the post,Im getting an error "Conversion failed when converting the varchar value '01:50:00' to data type int". 01:50 is correct that is the time difference, i think i know the problem my hours_off_day and hours_off_night fields are int, is this correct or should i change them to something else?

Thanks

quote:

Originally posted by visakh16

convert(varchar(8),dateadd(mi,sum(datediff(Minute, day_start, day_end)),0),108) as hours_off_day, convert(varchar(8),dateadd(mi,sum(datediff(Minute, night_start, night_end)),0),108) as hours_off_night

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 05:54:42

quote:
Originally posted by nt86

Visakh,

Thanks for the post,Im getting an error "Conversion failed when converting the varchar value '01:50:00' to data type int". 01:50 is correct that is the time difference, i think i know the problem my hours_off_day and hours_off_night fields are int, is this correct or should i change them to something else?

Thanks

quote:
Originally posted by visakh16

convert(varchar(8),dateadd(mi,sum(datediff(Minute, day_start, day_end)),0),108) as hours_off_day, convert(varchar(8),dateadd(mi,sum(datediff(Minute, night_start, night_end)),0),108) as hours_off_night

that shouldnt be a problem. can you show your full query please:?

niall

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-02-01 : 06:01:48

This is my Stored Procedure, I have underlined and put the datediff code in bold, thanks

ALTER PROCEDURE dbo.TEST_2RATES

AS

CREATE TABLE #savingstemp
(
pc_profile_id int,
shutdown_Time datetime NULL,
hibernate_Time datetime NULL,
sleep_Time datetime NULL,
startup_Time datetime NULL,
status varchar(50),
subpolicy_name varchar(50),
building_name varchar(50),
hours_off_day int,
day_hour_rate float,
hours_off_night int,
night_hour_rate float,
pc_kwh_rate float,
savings float
)

insert #savingstemp (pc_profile_id, shutdown_Time, startup_Time, status, subpolicy_name, building_name, day_hour_rate, night_hour_rate, pc_kwh_rate)

SELECT '2', '02/02/2009 11:02:08', '02/02/2009 16:03:03', 'CLOSED', 'Exempt', 'ORB', '1.2','3.9', '1.9'
/** Get hours off between Shutdown and Startup times*/
select pc_profile_id, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
shutdown_Time, startup_Time,day_start, day_end, night_start, night_end, datediff(hour, day_start, day_end) as hours_off_day,
datediff(hour, night_start, night_end) as hours_off_night
from (
select pc_profile_id, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
shutdown_Time, startup_Time, start1, finish1, start2, finish2, offset,
-- Either return null, or the start of the day period or the shutdown_time. The null value is to know when to stop counting
case
when startup_Time < case when shutdown_Time < start1 then start1 else case when shutdown_Time < finish1 then shutdown_Time else finish1 end
-- Provides a stoping condition, when the field day_start begins to be NULL
-- means that the day and night intervals don't fall in the shutdown/start period
end then null else
case when shutdown_Time < start1 then start1 else case when shutdown_Time < finish1 then shutdown_Time else finish1 end end
end as day_start,
-- Either returns the finish of the day period or the startup_time
case when startup_Time < finish1 then startup_Time else finish1 end as day_end,
-- Either returns the start of the night period or the startup_time
case when startup_Time > start2 then start2 else startup_Time end as night_start,
-- Either return the finish of the night period or the startup_time
case when startup_Time < finish2 then startup_Time else finish2 end as night_end
from (
-- For each record in the table provides de shutdown an start time
select pc_profile_id, shutdown_Time, startup_Time, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
-- Start time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '07:00:00', 120) start1,
-- Finish time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '22:59:00', 120) finish1,
-- Start time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset, shutdown_time), 120), 11) + '23:00:00', 120) start2,
-- Finish time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset + 1, shutdown_time), 120), 11) + '06:59:00', 120) finish2, offset
from #savingstemp cross join (
-- Provides a 10 row table with sequencial numbers from 0 to 9
-- This allows to have off states that last for 9 days
select top 10 row_number() over(order by id) -1 as offset
from syscolumns
) numbers
where status = 'CLOSED'
) periods
)a
-- Stopping condition
where day_start is not null
order by pc_profile_id, offset

RETURN

quote:
Originally posted by visakh16

quote:
Originally posted by nt86

Visakh,

Thanks for the post,Im getting an error "Conversion failed when converting the varchar value '01:50:00' to data type int". 01:50 is correct that is the time difference, i think i know the problem my hours_off_day and hours_off_night fields are int, is this correct or should i change them to something else?

Thanks

quote:
Originally posted by visakh16

convert(varchar(8),dateadd(mi,sum(datediff(Minute, day_start, day_end)),0),108) as hours_off_day, convert(varchar(8),dateadd(mi,sum(datediff(Minute, night_start, night_end)),0),108) as hours_off_night

that shouldnt be a problem. can you show your full query please:?

niall

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 06:06:08

where have sum() bits gone?

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-02-01 : 06:13:42

Sorry about that, Monday morning :( heres my original code with sample Data, thanks

ALTER PROCEDURE dbo.TEST_HOUR_MIN

AS

/* Create temp table */
CREATE TABLE #savingstemp
(
audit_id int,
pc_profile_id int,
shutdown_Time datetime NULL,
hibernate_Time datetime NULL,
sleep_Time datetime NULL,
startup_Time datetime NULL,
status varchar(50),
subpolicy_name varchar(50),
building_name varchar(50),
floor_name varchar(50),
room_name varchar(50),
hours_off_day int,
day_hour_rate float,
hours_off_night int,
night_hour_rate float,
pc_kwh_rate float,
savings float
)

/** Insert Values into Temp Table from View, including Day/Night Rates*/

insert into #savingstemp (audit_id, pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, building_name, floor_name, room_name, day_hour_rate, night_hour_rate, pc_kwh_rate)
SELECT '2', '3', '02/02/2009 11:02:08', NULL, NULL, '02/02/2009 16:03:03', 'CLOSED', 'Exempt', 'Building A', 'Floor2', 'Room A', '1.2','3.9', '1.9'

UPDATE #savingstemp
SET hours_off_day = isnull(savings.hours_off_day, 0) ,
hours_off_night = isnull(savings.hours_off_night, 0),
savings = (isnull(savings.hours_off_day, 0) * pc_kwh_rate * day_hour_rate) + (isnull(savings.hours_off_night, 0) * pc_kwh_rate * night_hour_rate)
FROM #savingstemp inner join (
select audit_id, sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night
from (
select audit_id, pc_profile_id, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
shutdown_Time, hibernate_Time, sleep_Time, startup_Time, start1, finish1, start2, finish2, offset,
-- Either return null, or the start of the day period or the shutdown_time. The null value is to know when to stop counting
case
when startup_Time < case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < start1 then start1 else case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < finish1 then COALESCE( shutdown_Time, hibernate_Time, sleep_Time) else finish1 end
-- Provides a stoping condition, when the field day_start begins to be NULL
-- means that the day and night intervals don't fall in the shutdown/start period
end then null else
case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < start1 then start1 else case when COALESCE( shutdown_Time, hibernate_Time, sleep_Time) < finish1 then COALESCE( shutdown_Time, hibernate_Time, sleep_Time) else finish1 end end
end as day_start,
-- Either returns the finish of the day rate or the startup_time
case when startup_Time < finish1 then startup_Time else finish1 end as day_end,
-- Either returns the start of the night rate or the startup_time
case when startup_Time > start2 then start2 else startup_Time end as night_start,
-- Either return the finish of the night rate or the startup_time
case when startup_Time < finish2 then startup_Time else finish2 end as night_end
from (
-- For each record in the table provides the shutdown, hibernate, sleep and start time
select audit_id, pc_profile_id, shutdown_Time, hibernate_Time, sleep_Time, startup_Time, status, subpolicy_name, day_hour_rate, night_hour_rate, pc_kwh_rate,
-- Start time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '07:00:00', 120) start1,
-- Finish time for the day period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '22:59:00', 120) finish1,
-- Start time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '23:00:00', 120) start2,
-- Finish time for the night period
convert(datetime, left(convert(varchar, dateadd(d, offset + 1, COALESCE( shutdown_Time, hibernate_Time, sleep_Time) ), 120), 11) + '06:59:00', 120) finish2, offset
from #savingstemp cross join (
-- Tally Table
select top 90 row_number() over(order by id) -1 as offset
from syscolumns
) numbers
where status = 'CLOSED'
) periods
)a
-- Stopping condition and calculations
where day_start is not null
group by audit_id
) Savings on #savingstemp.audit_id= Savings.audit_id

select * from #savingstemp

RETURN

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 06:16:29

how do you think you can store result as hours and minutes when you've defined your destination field as int? in that case you can only store it as x hours or x minutes not like hh:mm format

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-02-01 : 06:23:28

Thanks for the reply ya i was not sure about that, stupid mistake i must admit, should i define it as date time or what would be the best definition for it

quote:
Originally posted by visakh16

how do you think you can store result as hours and minutes when you've defined your destination field as int? in that case you can only store it as x hours or x minutes not like hh:mm format

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 06:37:05

you should define it as datetime

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-02-01 : 07:02:04

Thanks for the help ill give that a go,.

quote:
Originally posted by visakh16

you should define it as datetime

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 09:08:10

ok...great..let us know if you face any issues

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-02-01 : 09:53:38

After i change the hours_off_day and hours_off_night fields to datetime im getting an error "converting data type varchar to float" I presume its one of my fields which are defined as float but i tried changing them to numeric and i still get the error.

At the moment My SQL currently just calculates in hours but Im looking to calculate in hours and minutes so my savings will be more accurate.

Sorry if i have stupid mistakes still a newbie :(

quote:
Originally posted by visakh16

ok...great..let us know if you face any issues

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:38:00

you're using them in calculation for savings field which is float. so you need to convert them to int or float values before you do it (ie as x minutes rather than like hh:mi format)

nt86
Yak Posting Veteran

54 Posts

Posted - 2010-02-02 : 06:00:07

Visakh,

Thanks for the post, i think i may have solved my problem, i got my answer in the format of a decimal

select audit_id, sum(datediff(MINUTE, day_start, day_end)) / 60.0 AS hours_off_day, sum(datediff(hour, night_start, night_end)) AS hours_off_night
from (

so from there i can work out the savings, I have my fields hours_off_night and hours_off_day defined as float. Have to do a little more testing on it but from the looks of it, it seems to solve my problem and the answer Im getting seems very accurate :)

quote:
Originally posted by visakh16

you're using them in calculation for savings field which is float. so you need to convert them to int or float values before you do it (ie as x minutes rather than like hh:mi format)

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 06:04:29

good

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 06:11:36

quote:
Originally posted by nt86

Im currently using DATEDIFF to get the difference of two datetime fields in relation to hours, is it possible to to get the difference in hours and minutes?

I will need to calculate savings from this value so i will be multiplying my result by a number of fields so i guess ill need it as a decimal or something. How is this usually done, heres a little bit of my SQl this calculates the time in hours, im hoping to calculate hours and minutes

sum(datediff(hour, day_start, day_end)) as hours_off_day, sum(datediff(hour, night_start, night_end)) as hours_off_night

Ive tried this (code below) and it works and calculates in minutes but when i divide by 60 it then just displays the hours again i need both. my savings will be calculated on a per hour basis rate so that why i need it a way that i can calculate and multiple it out. I know theres easy ways around it like dividing my answer by 60 and that but Im just looking to see if there is a neat way of doing it.

sum(datediff(Minute, day_start, day_end)/60.0) as hours_off_day, sum(datediff(Minute, night_start, night_end)/60.0) as hours_off_night

Thanks

niall

Where do you want to show data?
If you use front end application, use format function there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources