February 16, 2017 at 3:49 am
Hi All,
I have tried several things and it's not doing quite what I need it to.
So I have this expression to get the first day of the month in SSIS to pass this variable to my query:
DATEADD("MONTH",DATEDIFF("MONTH",(DT_DATE)"1900-01-01",GETDATE()) ,(DT_DATE)"1900-01-01")
However at the end of the month, it's not pulling the last day of the month (I run the package every day to update a table rather than a monthly run).
So question, how would I adapt this to pick up the start of the month based on yesterday's date? This means on the 1st of each month it will always pick up the last day of the previous month thus getting the full month of data.
Thanks for your help on this, I can't do getdate()-1 because that brings the previous month. Which is not good for every day update.
February 16, 2017 at 3:53 am
But if you want 'start of the month based on yesterday's date', then on the first day of a month it should pull the first of the previous month.
What do you want the calculation to return for the following?
2017/01/01
2017/02/01
2017/02/28
2017/02/14
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2017 at 5:34 am
Kazmerelda - Thursday, February 16, 2017 3:49 AMHi All,I have tried several things and it's not doing quite what I need it to.
So I have this expression to get the first day of the month in SSIS to pass this variable to my query:
DATEADD("MONTH",DATEDIFF("MONTH",(DT_DATE)"1900-01-01",GETDATE()) ,(DT_DATE)"1900-01-01")
However at the end of the month, it's not pulling the last day of the month (I run the package every day to update a table rather than a monthly run).
So question, how would I adapt this to pick up the start of the month based on yesterday's date? This means on the 1st of each month it will always pick up the last day of the previous month thus getting the full month of data.
Thanks for your help on this, I can't do getdate()-1 because that brings the previous month. Which is not good for every day update.
It's hard to understand what you want. First of all, you ask about getting the first day of the month, but then you start talking about the last day of the month. Please clarify, with examples, as requested by Gail.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 16, 2017 at 6:41 am
One of these days, I will get it right.
Thanks Gail and Phil, I should have added dates apologies.
So, if I was running the report on the days outlined first below from Gail, this is what I would expect the start date to be:
2017/01/01 - expected start date 01/12/2016
2017/02/01 - expected start date 01/01/2017
2017/02/28 - expected start date 01/02/2017
2017/02/14 - expected start date 01/02/2017
Hope that is clearer, I get told off all the time for putting too much information/talking too much. Forget more is better here....
February 16, 2017 at 6:52 am
Edit: Didn't notice it was SSIS. Should read properly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2017 at 7:02 am
Try this and also note the example on how to give us some data to work with.
DECLARE @myTable TABLE (somedate DATE)
INSERT INTO @myTable
VALUES ('2017/01/01'), ('2017/02/01'), ('2017/02/28'), ('2017/02/14')
SELECT
DATEADD(DAY, 1, EOMONTH(DATEADD(dd,-1, somedate), -1))
FROM
@myTable
Cheers,
February 16, 2017 at 7:34 am
SSIS dateadd/datediff syntax is different from that in T-SQL. You could try this:
DATEADD("m", DATEDIFF("m", (DT_DATE) 0, DATEADD("d",-1,getdate())), (DT_DATE) 0)
Replacing Getdate() with your date, of course.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 16, 2017 at 9:51 am
So I finally have this, but Phil what you put helped focus me.
To get the first of the month:
DATEADD("MONTH",DATEDIFF("MONTH",(DT_DATE)"1900-01-01",DATEADD("d",-1,getdate())), (DT_DATE) "1900-01-01")
Really appreciate the time!
February 16, 2017 at 11:46 pm
Kazmerelda - Thursday, February 16, 2017 3:49 AMHi All,I have tried several things and it's not doing quite what I need it to.
So I have this expression to get the first day of the month in SSIS to pass this variable to my query:
DATEADD("MONTH",DATEDIFF("MONTH",(DT_DATE)"1900-01-01",GETDATE()) ,(DT_DATE)"1900-01-01")
However at the end of the month, it's not pulling the last day of the month (I run the package every day to update a table rather than a monthly run).
So question, how would I adapt this to pick up the start of the month based on yesterday's date? This means on the 1st of each month it will always pick up the last day of the previous month thus getting the full month of data.
Thanks for your help on this, I can't do getdate()-1 because that brings the previous month. Which is not good for every day update.
Hi,
you can get the end of date of current month EOMONTH(getdate(),0) , then add day into his and you get next month first date , like this . select DATEADD(day,1, EOMONTH(getdate(),0))
February 17, 2017 at 6:33 am
The easiest way to do this is:
DATEADD(mm, DATEDIFF(mm,0,getdate()-1), 0)
The inner part DATEDIFF(mm,0,getdate()-1) gets the number of months between yesterday and the date "1900-01-01 00:00:00.000". Then the outer part adds that number of months to yesterday's month to come up with the 1st day of the month for yesterday.
February 17, 2017 at 6:42 am
jadhav_vilas84 - Thursday, February 16, 2017 11:46 PMKazmerelda - Thursday, February 16, 2017 3:49 AMHi All,I have tried several things and it's not doing quite what I need it to.
So I have this expression to get the first day of the month in SSIS to pass this variable to my query:
DATEADD("MONTH",DATEDIFF("MONTH",(DT_DATE)"1900-01-01",GETDATE()) ,(DT_DATE)"1900-01-01")
However at the end of the month, it's not pulling the last day of the month (I run the package every day to update a table rather than a monthly run).
So question, how would I adapt this to pick up the start of the month based on yesterday's date? This means on the 1st of each month it will always pick up the last day of the previous month thus getting the full month of data.
Thanks for your help on this, I can't do getdate()-1 because that brings the previous month. Which is not good for every day update.
Hi,
you can get the end of date of current month EOMONTH(getdate(),0) , then add day into his and you get next month first date , like this . select DATEADD(day,1, EOMONTH(getdate(),0))
suzel.maccallum - Friday, February 17, 2017 6:33 AMThe easiest way to do this is:
DATEADD(mm, DATEDIFF(mm,0,getdate()-1), 0)The inner part DATEDIFF(mm,0,getdate()-1) gets the number of months between yesterday and the date "1900-01-01 00:00:00.000". Then the outer part adds that number of months to yesterday's month to come up with the 1st day of the month for yesterday.
You are in an SSIS forum, not T-SQL.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply