Manipulating GetDate()

  • Hello Everyone

    I need to find a way of manipulating getdate so that i can reverse it to anytime.

    So for example

    iF Getdate says the time is: 2006-06-23 01:06:23.857

    I have been trying to create a functionality in which I can manipulate getdate so that it returns 12 Midnight dynamically, so at the moment, the time is 01-06, it means I need to find a way to make Getdate deduct 1 hour and 6 minutes, so it selects/returns 12 midnight.

    In this case, I could have subtracted 1 hour 6 minutes, but as we know, Getdate is not static, so I need to be able to generate this dynamically

     

    Thanks

     


    Kindest Regards,

    John Burchel (Trainee Developer)

  • If all you want to do is have GETDATE() return a time of midnight no matter what the time is, this will do...

    SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    --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)

  • Thanks very much for your solution, this worked a treat, the other question is what if I need to make 12 midnight 4AM in the midnight ?


    Kindest Regards,

    John Burchel (Trainee Developer)

  • select

    dateadd(hour,4,dateadd(d,0,datediff(d,0,getdate())))

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • Thanks very much everyone.

    This has sorted me out

     


    Kindest Regards,

    John Burchel (Trainee Developer)

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

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