Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    7

    Unanswered: Extracting Hour from DateTime Field

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT DATEPART(hh,GetDate())
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2009
    Posts
    7
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    BEGIN and END should surround all your procedural code; so the RETURN statement must be inside these!
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    the problem is here
    Code:
    END IF
    No such statement "END IF". There is an "END", but no "END IF".

  6. #6
    Join Date
    Nov 2009
    Posts
    7
    Thanks for the reply, im after changing around my code and Im still getting a small error "incorrect syntax near "END"', sorry bit of a newbie to this, appreciate the help



    ALTER PROCEDURE dbo.UPDATE_TBL_PC_ON

    (
    @STARTUP_TIME datetime OUTPUT,
    @SHUTDOWN_TIME datetime OUTPUT
    )

    AS
    SELECT STARTUP_TIME, SHUTDOWN_TIME FROM TBL_PC_AUDIT



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



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

    END

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    ....
    As
    begin
    select startup_time, shutdown_time from tbl_pc_audit
    ....
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As Wim points out, you just have one more end than begin. You can think of them like parentheses.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •