Date to collate the whole month from the previous day date

Question

Post reply

Date to collate the whole month from the previous day date

Kazmerelda

SSCarpal Tunnel

Points: 4667
More actions
September 15, 2016 at 2:56 am

#330128

Hope I have explained this right!
So we want to get a "dead" figure for the previous months of our data once they have hit the 1st of the next month (so it takes the figures for the whole month).
I have set up a variable for my package but the start date is currently beginning of August so it goes back and changes the data (this affects one field which we are investigating, all other stats remain the same).
How can I get a command to say that the start date will always be the start of the month for the previous day?
So for example on 1st of the month, when this runs the start date would be 1st of the previous month. However on 2nd of the month it would be the current month.
Thanks in advance.

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

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

Thom A SSC Guru Points: 99129 More actions · Answer 1

Assuming in both instances that ReportDate is today's date, or the date the report is being run for.

SQL:

dateadd(month, datediff(month, 0, @ReportDate) - 1, 0)

SSIS Expression:

DATEADD("mm",DATEDIFF("mm", (DT_DBTIMESTAMP)"1/1/2000", @[User::ReportDate]) - 1, (DT_DBTIMESTAMP)"1/1/2000")

In both instances, this would return 01 August 2016 (the 1st of last month), either if ReportDate has a value of today's date, 15 September 2016, or the date this was expected to run, 01 September 2016.

Edit: Missed a quotation!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

benscooper01 Valued Member Points: 70 More actions · Answer 2

Both expressions keyed off GETDATE, but can be substituted if the report needs to appear that it's running on a different date

SQL

SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())), 0) AS DATE)

SSIS

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

Results when current date is one of the following dates:

'2016-08-31'Returns 2016-08-01

'2016-09-01'Returns 2016-08-01

'2016-09-15'Returns 2016-09-01

'2016-10-02'Returns 2016-10-01

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 3

I actually need to pass the date as a string in format yyyy-mm-dd, I knew I would forget to mention something!

So both ways worked, sadly my package fails because it's the wrong format to pass.

I have tried to work out how to do this, I got this far to be able to pass the correct format, but how would I incorporate this with what you have both helpfully given me?

I have had a play around, I got this far getting the right format:

RIGHT("0" + (DT_STR, 4, 1252) DATEPART("yyyy" , DATEADD("dd",-1,GETDATE())), 4) + "-" +

RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , DATEADD("dd",-1,GETDATE())), 2) + "-" +

(DT_STR, 2, 1252) DATEPART("dd" , DATEADD("dd",-1,GETDATE()))

Thanks again.

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 4

Ok you lot will be the only ones who will understand the fact that I cracked it with this:

RIGHT("0" + (DT_STR, 4, 1252) DATEPART("yyyy" , DATEADD("dd",-1,GETDATE())), 4) + "-" +

RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , DATEADD("dd",-1,GETDATE())), 2) + "-" +

"01"

Thanks for your help, as it did help.