Extracting hour from Datetime field

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

Extracting hour from Datetime field

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-20 : 11:04:57

I have a datetime field and trying to extract the hour from it in my store procedure, heres the code, would appreciate any help, thanks
Im getting the error "Hour is not a recognized built in function name"

CREATE PROCEDURE dbo.UPDATE_TBL_PC_ON
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS

BEGIN
IF HOUR(STARTUP_TIME) >= 7 AND HOUR(SHUTDOWN_TIME) <= 23 THEN
UPDATE TBL_PC_ON
SET HOURS_ON_DAY = DATEDIFF(HOUR, SHUTDOWN_TIME, STARTUP_TIME)
WHERE STATUS = 'CLOSED';
ELSE IF HOUR(STARTUP_TIME) <= 7 AND HOUR(SHUTDOWN_TIME) >= 23 THEN
UPDATE TBL_PC_ON
SET HOURS_ON_NIGHT = DATEDIFF(HOUR, SHUTDOWN_TIME, STARTUP_TIME)
WHERE STATUS = 'CLOSED';

END IF;
/* SET NOCOUNT ON */
RETURN

niall

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-20 : 11:07:46

datepart(hour,your_date)

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 11:15:07

quote:
Originally posted by webfred

datepart(hour,your_date)

No, you're never too old to Yak'n'Roll if you're too young to die.

Those Germans are just so damn efficient...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-11-20 : 14:56:00

...and stylish.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-21 : 11:54:10

No, you're never too old to Yak'n'Roll if you're too young to die.

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-23 : 06:33:51

quote:
Originally posted by webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks for the reply, Im still getting a small error, heres the code, the error is incorrect syntax near the keyword 'RETURN'. Thanks for the help :)

ALTER PROCEDURE dbo.UPDATE_TBL_PC_ON
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS

BEGIN

IF DATEPART(hh, STARTUP_TIME) >= 7 AND DATEPART(hh,SHUTDOWN_TIME) <= 23
UPDATE TBL_PC_ON
SET HOURS_ON_DAY = DATEDIFF(HOUR, SHUTDOWN_TIME, STARTUP_TIME)
WHERE STATUS = 'CLOSED';
ELSE IF DATEPART(hh, STARTUP_TIME) <= 7 AND DATEPART(hh, SHUTDOWN_TIME) >= 23
UPDATE TBL_PC_ON
SET HOURS_ON_NIGHT = DATEDIFF(HOUR, SHUTDOWN_TIME, STARTUP_TIME)
WHERE STATUS = 'CLOSED';

END IF
/* SET NOCOUNT ON */
RETURN

niall

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-23 : 06:54:22

see here please:
http://www.sqlteam.com/article/stored-procedures-returning-data

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources