January 19, 2015 at 11:20 am
What would be the correct GetDate date logic for the below in the where portion of my script? I need to get every Mondays date with: [register_date]Between(today-1) and (today-7) or [register_date]between(Sunday)and(Monday). For Example, Monday(01/19/15)data extract would be for register_date and(01/18/15)
January 19, 2015 at 11:30 am
In T-SQL, you'd use dateadd, with getdate() like this:
dateadd(dd, -1, getdate())
to get yesterday. In SSIS, if you are doing this, you'd use the Expression language, it's slightly different.
DATEADD("dd", -10, GETDATE())
Ref: http://www.sqlis.com/sqlis/post/Expression-Date-Functions.aspx - This has a discussion of date calculations.
January 19, 2015 at 11:54 am
Briceston (1/19/2015)
I have a script that will be used in SSIS to produce a data extract on weekly basis.What would be the correct GetDate date logic for the below in the where portion of my script? I need to get every Mondays date with: [register_date]Between(today-1) and (today-7) or [register_date]between(Sunday)and(Monday). For Example, Monday(01/19/15)data extract would be forregister_date and(01/18/15)
What's the destination for this data extract? I ask because it's very possible that SSIS is the wrong tool to use here.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2015 at 11:57 am
I'm using GetDate() in T-SQL. Below is the where portion of my script, which returns no data, there should be data for the intended dates.
WHERE [register _date ] BETWEEN DATEADD(day,-1,GETDATE()) AND DATEADD(day,-7,GETDATE())
January 19, 2015 at 12:06 pm
The destination will be in Excel. My problem is not with SSIS, my issues is what's the correct GetDate() and Dateadd syntax to use?
January 19, 2015 at 1:39 pm
Briceston (1/19/2015)
I'm using GetDate() in T-SQL. Below is the where portion of my script, which returns no data, there should be data for the intended dates.
WHERE [register _date ] BETWEEN DATEADD(day,-1,GETDATE()) AND DATEADD(day,-7,GETDATE())
You have the dates reversed and it will never return a thing. You code should be as follows...
WHERE [register _date ] BETWEEN DATEADD(day,-7,GETDATE()) AND DATEADD(day,-1,GETDATE())
... because the earliest date must come before the later date.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2015 at 8:56 pm
Thank you, this worked for me.
Jeff Moden (1/19/2015)
Briceston (1/19/2015)
I'm using GetDate() in T-SQL. Below is the where portion of my script, which returns no data, there should be data for the intended dates.
WHERE [register _date ] BETWEEN DATEADD(day,-1,GETDATE()) AND DATEADD(day,-7,GETDATE())
You have the dates reversed and it will never return a thing. You code should be as follows...
WHERE [register _date ] BETWEEN DATEADD(day,-7,GETDATE()) AND DATEADD(day,-1,GETDATE())
... because the earliest date must come before the later date.
January 20, 2015 at 4:51 am
If it's just Mondays you want (and *only* Mondays, no matter what day you were running the query on), you could use DATEPART(weekday,<yourEventDateCol>) = 1
in a WHERE clause ... ?
Theoretically, you could use the WEEK datepart as well to all of last week, depending on your datefirst..
Take a look at DATEPART http://msdn.microsoft.com/en-us/library/ms174420.aspx and DATEFIRST http://msdn.microsoft.com/en-us/library/ms181598.aspx
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
January 20, 2015 at 7:35 am
You bet. Thank you for your feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy