September 15, 2016 at 2:56 am
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.
September 15, 2016 at 3:54 am
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
September 15, 2016 at 4:13 am
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
September 15, 2016 at 7:32 am
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.
September 15, 2016 at 8:43 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply