Post reply

Dates and shift work

  • March 17, 2006 at 1:18 pm

    #168782

    We currently have 3 shifts working everyday

    5 AM - 12 PM, 1 PM - 8 PM, 9 PM - 4 AM

    I currently have a transaction datetime and need to convert it to two fields

    Shift Date work done, and Hour.  My dilemna is how to do this.  In MSACCESS you can do this but it does not work in SQL

    Work Date: DateSerial(Year(Pack_Production_Transaction_datetime-(5/24)),Month(Pack_Production_Transaction_datetime-(5/24)),Day(Pack_Production_Transaction_datetime-(5/24)))

    Time: (Pack_Production_Transaction_datetime-5/24)-Int(Pack_Production_Transaction_datetime-5/24)

     

    So basically if the datetime comes over like this

    '3/5/2006 4:27:45 AM' I need it to read like this 3/4/2006 23:27:45 and when

    it looks like this

    '3/5/2006 5:27:45 AM' I need it to read like this 3/5/2006 0:27:45 and so on

    Any help is appreciated

  • March 18, 2006 at 7:45 pm

    #627481

    Basically, your saying you want to subtract 5 hours from anygiven datetime... this'll do just that...

    SELECT DATEADD(hh,-5,somedatetime)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 20, 2006 at 8:03 am

    #627627

    Ya know I guess I was making a mountain out of a mole hill. Yes that will work. I appreciate you helping me with that

  • March 21, 2006 at 6:50 pm

    #628003

    Not a problem... wasn't sure that's what you wanted but glad to help...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply