locked
Last day of previous month (SSIS expression) RRS feed

  • Question

  • Hi All,

    I get the last day of previous month in below format.

    (DT_WSTR,200)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()), DATEADD("m", 0, GETDATE()))

    But I want to get last day of previous month in the below format.

    2014_04_30


    -kccrga http://dbatrend.blogspot.com.au/

    Tuesday, May 6, 2014 11:26 PM

Answers

  • Hi Kccrga,

    In SQL Server Integration Services, you can use the following expression to get the last day of previous month:

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

    If you can use an Execute SQL Task, you can try Raunak's SQL statement.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, May 18, 2014 4:47 PM
    Tuesday, May 13, 2014 7:44 AM
  • Hi All,

    I get the last day of previous month in below format.

    (DT_WSTR,200)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()), DATEADD("m", 0, GETDATE()))

    But I want to get last day of previous month in the below format.

    2014_04_30


    -kccrga http://dbatrend.blogspot.com.au/

    just add a replace over it

    REPLACE((DT_WSTR,200)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()), DATEADD("m", 0, GETDATE())),"-","_")


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Mike Yin Sunday, May 18, 2014 4:47 PM
    Tuesday, May 13, 2014 8:08 AM

All replies

  • Have you tried using the execute SQL task to execute the query and store the result in a variable?

    select REPLACE(CAST(DATEADD("d", -DAY(GETDATE()), DATEADD("M", 0, GETDATE())) as DATE),'-','_')



    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    • Marked as answer by Mike Yin Tuesday, May 13, 2014 7:31 AM
    • Unmarked as answer by Mike Yin Tuesday, May 13, 2014 7:35 AM
    Wednesday, May 7, 2014 8:29 AM
  • Hi Kccrga,

    In SQL Server Integration Services, you can use the following expression to get the last day of previous month:

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

    If you can use an Execute SQL Task, you can try Raunak's SQL statement.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, May 18, 2014 4:47 PM
    Tuesday, May 13, 2014 7:44 AM
  • There is a new function EOMONTH in SQL Server 2012:

    DECLARE @date DATETIME = '12/1/2011';
    SELECT EOMONTH ( @date ) AS Result;



    SQLhint.com

    Tuesday, May 13, 2014 7:52 AM
  • Hi All,

    I get the last day of previous month in below format.

    (DT_WSTR,200)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()), DATEADD("m", 0, GETDATE()))

    But I want to get last day of previous month in the below format.

    2014_04_30


    -kccrga http://dbatrend.blogspot.com.au/

    just add a replace over it

    REPLACE((DT_WSTR,200)(DT_DBDATE)DATEADD("d", -DAY(GETDATE()), DATEADD("m", 0, GETDATE())),"-","_")


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Mike Yin Sunday, May 18, 2014 4:47 PM
    Tuesday, May 13, 2014 8:08 AM