query to show number of days left or overdue

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

SQL Server 2000 Forums

SQL Server Development (2000)

query to show number of days left or overdue

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2005-10-18 : 16:40:20

I have the table tasks:

I want to query the table tasks to show the tasks for a user and the number of days left or is it over due.

field names i have is:
project, task, due_date

if the due date is 25th of october then: it should show as 7 days

if the due_date is 15th of october then it should say 3 days over due.

select project, task, days(using the field due date which has the date in it.)

Can you please help me with the query.

Thank you very much for the information.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-18 : 17:22:49

Lookup the GetDate() function, which returns the current date and time.
Also lookup DateDiff(), which can calculate the days, hours, minutes, or other intervals of time between two datetime values.

Kristen
Test

22859 Posts

Posted - 2005-10-19 : 01:48:06

[code]
DECLARE @MyTable TABLE
(
project varchar(10),
task varchar(10),
due_date datetime
)

INSERT INTO @MyTable
SELECT 'PROJ1', 'TASK1', '15 Oct 2005' UNION ALL
SELECT 'PROJ2', 'TASK1', '25 Oct 2005' UNION ALL
SELECT 'PROJ3', 'TASK1', '19 Oct 2005' -- Today

select project, task,
[days] = CASE WHEN due_date < GetDate()
THEN CONVERT(varchar(20), DATEDIFF(Day, due_date, GetDate()))
+ ' days over due'
ELSE CONVERT(varchar(20), DATEDIFF(Day, GetDate(), due_date))
+ ' days'
END
FROM @MyTable
[/code]
Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 01:55:12

Kris, Left will do implicit conversion


select project, task, 
       [days] = CASE WHEN due_date < GetDate()
                         THEN Left(DATEDIFF(Day, due_date, GetDate()),10)
                                 + ' days over due'
                     ELSE Left(DATEDIFF(Day, GetDate(), due_date),10)
                                 + ' days'
                     END
FROM @MyTable

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-19 : 01:56:46

"Left will do implicit conversion"

Didn't know that, but I can't see me using it as its way too obtuse!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 02:05:56

Yes. But thats just for an example