Date Range Formula

Question

Date Range Formula

bpowers

SSCertifiable

Points: 5483
More actions
February 3, 2015 at 12:17 pm

#314211

How would we dynamically query last years data from the month/day we kick off the query, to the first day of the same month in the previous year? For example if we run the query today ,2/3/2015, we want it to pull records back to 2/1/2014. Any feedback will be appreciated.
Simple Example Code:
SELECT DOC_NO
,ENTER_DATE
FROM SALES_ORDER
WHERE ENTER_DATE between '2/3/2015' and '2/1/2014'--Need to calculate dynamically

Viewing 7 posts - 1 through 6 (of 6 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

To get dynamic calculations on dates, you can use the DATEADD() function.

Remember that when using BETWEEN, the lower value goes first or you'll get a non-valid range with no error and no rows returned.

The following article can help you with date routines: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Like this:

declare @RunDate date = getdate();

select @RunDate, dateadd(year,-1,dateadd(month,datediff(month,0,@RunDate),0));

SELECT

DOC_NO

,ENTER_DATE

FROM

SALES_ORDER

WHERE

ENTER_DATE between dateadd(year,-1,dateadd(month,datediff(month,0,@RunDate),0)) and @RunDate --Need to calculate dynamically

Please note that the earlier date in the range needs to be first in the BETWEEN clause.

ScottPletcher SSC Guru Points: 101101 More actions · Answer 3

Best is to always use >= and < on date/datetimes, not between:

SELECT DOC_NO

,ENTER_DATE

FROM SALES_ORDER

WHERE ENTER_DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)

AND ENTER_DATE < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

ScottPletcher (2/3/2015)
Best is to always use >= and < on date/datetimes, not between:
SELECT DOC_NO
,ENTER_DATE
FROM SALES_ORDER
WHERE ENTER_DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
AND ENTER_DATE < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)

Agree 100%.

ScottPletcher SSC Guru Points: 101101 More actions · Answer 5

Lynn Pettis (2/3/2015)
ScottPletcher (2/3/2015)
Best is to always use >= and < on date/datetimes, not between:
SELECT DOC_NO
,ENTER_DATE
FROM SALES_ORDER
WHERE ENTER_DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)
AND ENTER_DATE < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
Agree 100%.

For the record, I hadn't yet seen other comments when I posted mine. My comment was just a general observation 😉 :cool:.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

bpowers SSCertifiable Points: 5483 More actions · Answer 6

bpowers

SSCertifiable

Points: 5483

February 3, 2015 at 8:33 pm

#1775185

Worked perfect! Thanks for all the help!