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.

    REPLACE(LEFT((DT_WSTR,20)DATEADD("d",-1,DATEADD("m",DATEDIFF("m",(DT_DATE)"1901-01-01",GETDATE()),(DT_DATE)"1901-01-01")),10),"-","/")

     

    Thanks!

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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.

    CONVERT(DT_STR,REPLACE(LEFT((DT_WSTR,20)DATEADD("d",-1,DATEADD("m",DATEDIFF("m",(DT_DATE)"1901-01-01",GETDATE()),(DT_DATE)"1901-01-01")),10),"-","/"),101)

    • This reply was modified 4 weeks ago by  SQL Server.
  • look at this http://bilearninghub.blogspot.com/2018/05/format-date-in-ssis-mm-dd-yyyy-or.html

    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:

    SELECT CONVERT(VARCHAR(10), GETDATE(), 101);

    For the last day of the previous month:

    SELECT EOMONTH(DATEADD(MONTH,-1,GETDATE()));

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

    SELECT CONVERT(VARCHAR(10), EOMONTH(DATEADD(MONTH,-1,GETDATE())), 101);
    "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 5 (of 5 total)

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