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: Monday, August 11, 2014 3:12 PM
Points: 12, Visits: 139
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: Yesterday @ 5:43 PM
Points: 3,667, Visits: 8,006
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.
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?

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: Monday, August 11, 2014 3:12 PM
Points: 12, Visits: 139

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