Last day of the previous month - Format MM/DD/YYYY

  • I use the below to get Last day of Previous Month, but I want the date format to be MM/DD/YYYY.  We are sending that in the output file.




  • Lookup the key word CONVERT.  You'll need to use the format number of 101.


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


    You mean like this? This is throwing error. I thought the convert 101 is for T-SQL.


    • This reply was modified 2 years, 5 months ago by  SQL Server.
  • look at this

    RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)+"-"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

    as you are doing a bit of calculation with the dates I would advise that that is done on an earlier step and then use that variable/column on the final formatting formula.

  • As Jeff mentioned, CONVERT + 101 gets you the formatting you need:


    For the last day of the previous month:


    For the last day of the previous month formatted as needed:

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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