Post reply

Reformat datetime as: mm/dd/yyyy

  • December 8, 2005 at 8:47 am

    #96062

    This SQL:

    select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0)) As MonthEndDate

    Returns: 12/1/2005 12:00:00 AM

    How can I modify the above SQL to return: 12/1/2005   

    thanks in advance

     

     

    BT

  • December 8, 2005 at 9:00 am

    #608442

    use the convert function...

    <pre class="code"

    select convert(varchar, dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)), 101) As MonthEndDate







    **ASCII stupid question, get a stupid ANSI !!!**

  • December 8, 2005 at 11:45 pm

    #608585

    Ummmm.... since when is 12/01/2005 a month end date?

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

  • December 9, 2005 at 4:04 am

    #608607

    Yeah.. and since when the posted SQL returns 12/1/2005 12:00:00 AM? At least for me it returns

    2005-12-31 23:59:59.997

    Anyway, it seems that question was how to strip the time part away from result, and that was answered by sushila.

  • December 9, 2005 at 7:15 am

    #608642

    look in books online at the set dateformat command.  But you should always try to get dates in a non-ambiguous format, such as YYYY-MM-DD (or better yet, as a native date/time floating point value) and let your client deal with formatting.

    But to answer your question, run

    set dateformat mdy

  • December 9, 2005 at 5:21 pm

    #608849

    Vladan and Sushila are correct... you may also just try finding the first day of the next month and the looking for anything less than that.

    --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 6 posts - 1 through 5 (of 5 total)

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