Again with the subtraction of dates...

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 2005 Forums

Transact-SQL (2005)

Again with the subtraction of dates...

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2008-12-24 : 15:15:00

I'm sorry. I know this has been discussed ad naseum... but I'm hitting a wall, and none of the examples I can find seem to fit my scenario -- and I'm an examples kind of gal when it comes to learning new things. I have read about the issues with DATEDIFF, so I understand why I'm not getting the results I want, but how do I get the results I need?

What I am trying to do is subtract a date field (Due Date) in the database from today's date. In theory, I should have due dates in the past which would result in a negative number and Due Dates in the future which would result in a positive number. (AKA -- DOS or Days Outstanding)

What I am getting is ALL negative numbers - and the numbers are just wrong anyway.

Here is what one of my tries looks like with it's results:

SELECT
convert(char,INV.DUE_DATE,110) DueDate,
TodaysDate=convert(varchar, GETDATE(), 110),
DATEDIFF(day, convert(varchar,getdate(),110), INV_DATE) AS DOS

from INV
where INV.STAT IN (3,4,5,6,17)

RESULTS
-------
DueDate Todays Date DOS
01-01-2009 12-24-2008 -22
10-25-2008 12-24-2008 -90
12-21-2008 12-24-2008 -33
12-18-2008 12-24-2008 -36
10-03-2008 12-24-2008 -112

Here is another one I tried - the difference is I used CAST:

select
convert(char,INV.DUE_DATE,110) DueDate,
TodaysDate=convert(varchar, GETDATE(), 110),
CAST(datediff(day, getdate(), INV_DATE)as varchar)

from INV
where INV.STAT IN (3,4,5,6,17)

RESULTS

DUE DATE Todays Date DOS
01-01-2009 12-24-2008 -22
10-25-2008 12-24-2008 -90
12-21-2008 12-24-2008 -33
12-18-2008 12-24-2008 -36
10-03-2008 12-24-2008 -112

Thanks for any help you can give,
Sherri

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-24 : 15:29:20

Just reverse the order of the parameters that you are passing to DATEDIFF. Also, don't convert or cast the data, just keep them as datetime.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2008-12-24 : 16:08:18

I tried that, but it gives a different and equally wrong response -- also only 1 of the rows returned had a negative amount. If you look at the DueDate and Todays Date there is more than 5, 6, 7 or 8 days difference.

Due Date Invoice Date Days Outstanding
01-15-2009 12-24-2008 8
01-16-2009 12-24-2008 7
01-17-2009 12-24-2008 6
01-18-2009 12-24-2008 5
01-21-2009 12-24-2008 2
01-25-2009 12-24-2008 -2

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-24 : 23:07:15

select datediff(dd,'12-24-2008','01-25-2009')
o/p is 32

select duedate,invoicedate,datediff(dd,invoicedate,duedate) as daysoutstanding from urtable

don't need to convert as varchar as tkizer said, keep as datetime column only

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2008-12-26 : 14:20:57

OK. So I did that. New Code:

SELECT
convert(char,INV.DUE_DATE,110) DueDate,
TodaysDate=convert(varchar, GETDATE(), 110),
DATEDIFF(day, getdate(), INV_DATE) AS DOS

from INV
where INV.STAT IN (3,4,5,6,17)
order by DUE_DATE

And here are the results:

There is more than a -8 difference between 12/26/08 and 01/17/09. Same for the rest of it.

And how can 07/09/06 be -931 days past due and 01/17/08 8 days past due? 01/17/08 shouldn't be past due at all. And look at the last one.... It says there is 0 days between 12/26/08 and 01/25/09?

07-09-2006 12-26-2008 -931
01-17-2009 12-26-2008 -8
01-17-2009 12-26-2008 -8
01-18-2009 2-26-2008 -7
01-18-2009 12-26-2008 -7
01-25-2009 12-26-2008 0

Thank you for any further help anybody can give me.

Sherri

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-26 : 14:34:17

Does the two columns INV.DUE_DATE and INV.INV_DATE contain the same data?
In your query, you are retrieving INV.DUE_DATE in the first column.
For calculating the third column, you are using INV.INV_DATE.
Add one more column to the query to show INV.INV_DATE, so you can see what is in INV.INV_DATE.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-26 : 14:47:06

Post the results of this query:

SELECT DUE_DATE AS DueDate, GETDATE() AS TodaysDate, INV_DATE AS InvDate,
DATEDIFF(day, GETDATE(), INV_DATE) AS DayDiff
FROM INV
WHERE STAT IN (3, 4, 5, 6, 17)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2008-12-26 : 15:06:23

SunitaBeck: INV_DATE is the date the customer was billed. DUE_DATE is the date the bill is due. I see that I goobered that last query by mixing the fields.

TKizer, I changed the code to reflect SunitaBeck's observations and the results are:
DUE DATE TODATES DATE DAYDIFF
2008-05-31 01:00:00.000 2008-12-26 12:03:49.967 -209
2008-06-19 00:00:00.000 2008-12-26 12:03:49.967 -190
2008-06-24 00:00:00.000 2008-12-26 12:03:49.967 -185
2008-06-29 00:00:00.000 2008-12-26 12:03:49.967 -180
2008-07-01 00:00:00.000 2008-12-26 12:03:49.967 -178
2009-01-17 00:00:00.000 2008-12-26 12:03:49.967 22
2009-01-18 00:00:00.000 2008-12-26 12:03:49.967 23
2009-01-18 00:00:00.000 2008-12-26 12:03:49.967 23
2009-01-21 00:00:00.000 2008-12-26 12:03:49.967 26
2009-01-25 00:00:00.000 2008-12-26 12:03:49.967 30

THANK YOU, THANK YOU, THANK YOU!
Sherri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources