time question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

time question

Author

Topic

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 09:58:27

this should be easy but i cant figure it out. to do a time range for field name date1. I want to have it say get all current records between current date and 120 days before.

This needs to be sql format

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 10:02:55

where datecol>=dateadd(day,datediff(day,0,getdate()-120),0)

Madhivanan

Failing to plan is Planning to fail

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 10:24:12

ok i think I am confused the code you gave me is not working when i place it in the where section. The field that i need to go off of is called ardetl.arduedate. i need that line of code you just gave me to go off of those dates coming from that table and field.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-31 : 10:26:01

so...
where ardetl.arduedate>=dateadd(day,datediff(day,0,getdate()-120),0)

if that's what you tried and it didn't work, show us your full query

Em

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 10:39:34

its showing some negative dates now. It seems to be working but what I am noticing is it is going from 120 down to 0 and counting back up in negatives. aka 5,4,3,2,1,-1,-2,-3,-4,-5

SELECT custmast.custname AS manufacturer, ardetl.arinvno, ardetl.artotal, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days, ardetl.kcustnum,custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate

FROM ardetl INNER JOIN custmast ON ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000') AND ardetl.arduedate >= DATEADD(day, DATEDIFF(day, 0, GETDATE() - 120), 0))
ORDER BY manufacturer, ardetl.arduedate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 12:48:58

quote:
Originally posted by midpenntech

its showing some negative dates now. It seems to be working but what I am noticing is it is going from 120 down to 0 and counting back up in negatives. aka 5,4,3,2,1,-1,-2,-3,-4,-5

SELECT custmast.custname AS manufacturer, ardetl.arinvno, ardetl.artotal, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days, ardetl.kcustnum,custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate

FROM ardetl INNER JOIN custmast ON ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000') AND ardetl.arduedate >= DATEADD(day, DATEDIFF(day, 0, GETDATE() - 120), 0))
ORDER BY manufacturer, ardetl.arduedate

the negative dates are because some of the ardetl.arduedate values are having values greater than the current date so that
ardetl.arduedate) * - 1 AS Days will return -ive value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources