How to get first day of next month in variable

Question

Post reply

How to get first day of next month in variable

Kazmerelda

SSCarpal Tunnel

Points: 4667
More actions
September 27, 2016 at 7:34 am

#317851

Hi All,
I have got this (with help from you guys) with how to get the first of the current month:
"01" + "/" +
RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , DATEADD("dd",-1,GETDATE())), 2) + "/" + RIGHT("0" + (DT_STR, 4, 1252) DATEPART("yyyy" , DATEADD("dd",-1,GETDATE())), 4)
So how would I adjust this to get the first of the next month? I have had a play around and googled but am not really finding anything that works in SSIS but plenty for T-SQL.
Thanks in advance

Viewing 11 posts - 1 through 10 (of 10 total)

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

Spiff SSCertifiable Points: 7826 More actions · Answer 1

Hi Kazmerelda,

The approach I usually take for things like this is to adjust the dates rather then try to parse strings.

DATEADD("MONTH",DATEDIFF("MONTH",(DT_DATE)"1900-01-01",GETDATE()) + 1,(DT_DATE)"1900-01-01")

We get the number of months between the 01/01/1900 (use 01/01/1900 as the 0 date in SSIS is 30-12-1899 for some reason) and the current date.

Add 1 to that then add that number of full months to 01-01-1900, so it always lands on the 1st of the month.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

I don't know if this will help you in SSIS or not, but it's very efficient if it can.

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0);

This is from Lynn's page at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 3

Spiff, normally I would use that but I am pulling data from Oracle which is a whole world all by itself it seems and does not like that at all. I am going blind from all the " and + for the parsing.

Thanks both I will keep battling and playing, will post back once I crack it.

Spiff SSCertifiable Points: 7826 More actions · Answer 4

The code I posted is a SSIS expression so it can be used in the derived column transform.

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 5

I am using the expression to pass the dates via a variable to the query variable so at that point can't transform, unless I am missing something (finally using my course skills from my SSIS course earlier this year).

Ken McKelvey SSCoach Points: 18843 More actions · Answer 6

SELECT ADD_MONTHS(TRUNC(sysdate, 'MONTH'), 1)

FROM DUAL;

Spiff SSCertifiable Points: 7826 More actions · Answer 7

The expression can be used as a Variable's expression, however Ken's solution kind of makes it a moot point now. 🙂

MMartin1 One Orange Chip Points: 27795 More actions · Answer 8

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.

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

Spiff SSCertifiable Points: 7826 More actions · Answer 9

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?

MMartin1 One Orange Chip Points: 27795 More actions · Answer 10

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

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