Calculating DatePart - SQL Server Forums

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

Calculating DatePart

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-27 : 11:23:05

Hey,
I need to calculate the HOURS_OFF where ill have to use datepart to calculate the difference between two datetimes in a stored procedure. I have a number of fields STARTUP_TIME, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME. I need to get the difference between STARTUP_TIME and SHUTDOWN_TIME and then HIBERNATE_TIME and STARTUP_TIME and finally SLEEP_TIME and STARTUP_TIME, these 3 calculations then need to be added together. Ive attached below sample code, would appreciate any help thanks very much.

SET @hours_off_day = DATEPART(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME)
WHERE STATUS = 'CLOSED';
SET @hours_off_day = DATEPART(HOUR, @HIBERNATE_TIME, @STARTUP_TIME)
WHERE STATUS = 'CLOSED';
SET @hours_off_day = DATEPART(HOUR, @SLEEP_TIME, @STARTUP_TIME)
WHERE STATUS = 'CLOSED';

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-27 : 22:14:10

seems like what you need is datediff rather than datepart

DATEDIFF(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME) + DATEDIFF(HOUR, @HIBERNATE_TIME, @STARTUP_TIME) + DATEDIFF(HOUR, @SLEEP_TIME, @STARTUP_TIME)

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-28 : 14:44:21

Thanks for the post, appreciate it, I now just have to distinguish what part of the day these hours fall between, for example if they are between 7:00AM and 23:00PM they will be categorized as DAY_RATE, otherwise they will be categorized NIGHT_RATE. What would be the best way of doing this, I appreciate the help, Im just a bit of a newbie when its comes to using Stored procedures :(

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-29 : 12:42:03

are DAY_RATE & NIGHT_RATE fields in table? or are they variables?

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-29 : 12:51:21

quote:
Originally posted by visakh16

are DAY_RATE & NIGHT_RATE fields in table? or are they variables?

Thanks for getting back to me, They are fields in a table but they are also variables, the user determines the rate by entering it themselves.

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-29 : 12:56:54

sorry didnt understand that. so from where you want to fetch the value? variables or fields?

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-30 : 04:25:14

quote:
Originally posted by visakh16

sorry didnt understand that. so from where you want to fetch the value? variables or fields?

Sorry, Well ill be fetching them from a field, however there will only be one set DAT_RATE and one set NIGHT_RATE.

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 09:38:07

then i think this is what you need


SELECT ...,CASE WHEN DATEPART(hh,dateval) BETWEEN 7 AND 23 THEN DAY_RATE ELSE NIGHT_RATE END,...

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-30 : 10:42:47

quote:
Originally posted by visakh16

then i think this is what you need

SELECT ...,CASE WHEN DATEPART(hh,dateval) BETWEEN 7 AND 23 THEN DAY_RATE ELSE NIGHT_RATE END,...

Thanks for the post, ill try that and see thanks

niall

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 10:53:37

good luck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources