Date problem - 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

Date problem

Author

Topic

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-28 : 22:23:20

i want to get the date the following using SQL

*with due date within 1-30 days of query date
datediff(dd,dueDate, getdate() > 30) -- am i correct?

*with due date within 31-60 days of query date
datediff(dd,dueDate, getdate() > 30
datediff(dd,dueDate, getdate() < 60

with due date within 61-90 days of query date
datediff(dd,dueDate, getdate() > 60
datediff(dd,dueDate, getdate() < 90

are the above code i write are correct? Please advice. Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 22:31:29

Did you ever try to run your query at all ? if you do, you will notice all the syntax error

quote:
*with due date within 1-30 days of query date
datediff(dd,dueDate, getdate() > 30) -- am i correct?

should be :

datediff(dd, dueDate, getdate()) > 30

better with :

dueDate > dateadd(day, datediff(day, 0, getdate()), 30)

as applying functions on your column means SQL Server will not be able to utilize any indexes on that.

the rest you should be able to figure it out yourself.

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-28 : 22:38:36

no i haven't tried it yah it will error. why is better to use dueDate > dateadd(day, datediff(day, 0, getdate()), 30) where they both return the same result?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 22:46:18

yes. both will give you the same result.

When you use a function on a column, it will result in a table scan to compute the result of the function before the comparison ( > 30).

as for the way i use, the "dateadd(day, datediff(day, 0, getdate()), 30)" is evaluated and the result is a date and basically the comparison is just dueDate > '2009-06-28' and if you have index on the dueDate column, SQL Server will be able to make use of that index.

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-28 : 23:00:03

I see.. Thank you but you query dueDate > dateadd(day, datediff(day, 0, getdate()), 30) is returning date in the future like 2010 which will not satisfy to this statement *with due date within 1-30 days of query date*?

i am assuming this code is correct?
duedate > dateadd(day, datediff(day, 0, getdate()), 1)
AND duedate <= dateadd(day, datediff(day, 0, getdate()), 30)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 23:07:51

that was based on your original query "datediff(dd,dueDate, getdate() > 30)"

for dueDate within 30 days of current date

dueDate < dateadd(day, datediff(day, 0, getdate()), 30)

use <= if you want the date to be inclusive

so for 31 - 60 days


    dueDate >  dateadd(day, datediff(day, 0, getdate()), 30)
and dueDate <= dateadd(day, datediff(day, 0, getdate()), 60)

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-28 : 23:10:56

yes.. so for within 30 days

duedate > dateadd(day, datediff(day, 0, getdate()), 1)

AND duedate <= dateadd(day, datediff(day, 0, getdate()), 30)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 23:13:54

quote:
Originally posted by Limuh

yes.. so for within 30 days

duedate > dateadd(day, datediff(day, 0, getdate()), 1)

AND duedate <= dateadd(day, datediff(day, 0, getdate()), 30)

yes.
use > or >= and < or <= depending on how you want the date to be inclusive or exclusive.

KH
[spoiler]Time is always against us[/spoiler]

Limuh
Yak Posting Veteran

94 Posts

Posted - 2009-05-28 : 23:40:54

Thank you! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources