Home Forums Data Warehousing Integration Services How to get first day of next month in variable RE: How to get first day of next month in variable

  • September 29, 2016 at 4:25 pm

    #1904073

    Spiff (9/29/2016)

    MMartin1 (9/29/2016)

    In the SSIS expression builder , enter:

    (DT_DATE)((DT_WSTR, 4) year(getdate())+"-"+ REPLICATE( "0", 2-LEN((DT_WSTR, 2)(month(getdate())+1) )) + (DT_WSTR, 2)( month(getdate())+1) +"-01")

    EDIT : This will give you the first of the next month, substitute the '+1' with a '+0' after the 'getdate()' parts of the MONTH function to get the first of the current month.

    Doesn't this return a month of 13 if the current date is in December?

    It does yes, thanks for catching. I should have bothered to test it more.

    Here is the corrected version :

    DateAdd("M", 1, (DT_DATE)((DT_WSTR, 4) year(getdate())+"-"+ REPLICATE( "0", 2-LEN((DT_WSTR, 2)(month(getdate())) )) + (DT_WSTR, 2)( month(getdate())) +"-01"))

    If you enter a 4 as the second argument to the dateadd function , it will automatically correctly return 2017-01-01

    ----------------------------------------------------