Post reply

Getting current date as part of an Stored Procedure

  • August 12, 2011 at 7:47 pm

    #1368292

    Ask for a redesign to merge the 2 columns. This makes no sense and will always cause you problems down the road.

  • August 12, 2011 at 8:39 pm

    #1368299

    doug 40899 (8/12/2011)

    Actually ... in going back through my code, I realized I missed something. The time and date for this process is stored in two fields. Start time and start date. Both of which are timestamp values. So I think that I'll have to do a datepart for both portions of the query in the Where clause.

    DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,

    DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))

    that query gives me a start time and date.

    DATEADD(MINUTE, mOnCallDelete.duration,

    DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

    DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')))

    that gives me an end time. So my query will have to be against both those portions to make sure that it's not after the getdate. Any suggestions on how to do that?

    I agree with Remi... having date and time in separate columns is a real killer. There's no chance of using an index properly to do the datetime lookups you want because you have to add the two columns together to do it right.

    If you can't change those two columns, ask if you can add an indexed calculated column and then we can get on with the problem correctly.

    --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)
    Intro to Tally Tables and Functions

Viewing 2 posts - 31 through 31 (of 31 total)

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