Using GetDate() between two dates

Question

Post reply

Using GetDate() between two dates

Briceston

SSCrazy

Points: 2899
More actions
January 19, 2015 at 11:20 am

#300418

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)

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 1

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Briceston SSCrazy Points: 2899 More actions · Answer 3

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())

Briceston SSCrazy Points: 2899 More actions · Answer 4

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?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Briceston SSCrazy Points: 2899 More actions · Answer 6

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.

sqlmunkee SSCommitted Points: 1949 More actions · Answer 7

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]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

You bet. Thank you for your feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions