move calculation to right side

Question

move calculation to right side

Wrangler

Grasshopper

Points: 12
More actions
January 16, 2014 at 10:13 am

#287379

I'm having issues with the following query. I'd like to use the index on the Date_Created field so the dateadd needs to move.
Original:
select * from theTable
where
dateadd(day,364,Date_Created)
between DATEADD(day,-8,'2013-12-25') and '2013-12-25' )
Mine:
select * from theTable
where
date_created
between dateadd(day,-373,'2013-12-25') and dateadd(day,-365,'2013-12-25')
Does this look correct?

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

You have a difference, but it's just by one day.

The easiest way to test this is to run a query against a calendar table that will return all dates and one row per date.

select * from theTable

where

Date_Created

between dateadd(day,-372,'2013-12-25') and dateadd(day,-364,'2013-12-25')

ORDER BY Date_Created

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

Wrangler Grasshopper Points: 12 More actions · Answer 2

I created the table as suggested and tested. This is what I found worked:

select date_created as new from caltest

where

Date_Created between dateadd(day,-372,'2013-12-25') and dateadd(day,-363,'2013-12-25')

ORDER BY Date_Created

Thank you