Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

move calculation to right side Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 3:22 PM
Points: 12, Visits: 136
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?
Post #1531680
Posted Thursday, January 16, 2014 10:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,374, Visits: 7,303
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1531688
Posted Thursday, January 16, 2014 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 3:22 PM
Points: 12, Visits: 136

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
Post #1531760
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse