April 20, 2017 at 11:22 am
I was helping one of my new employees with a script that was being written to determine date value data. While trying to determine the number of days between the present and when an event occurred, the theory was that a simple Date 1 minus Date 2 would provide the requested answer (simple mathematical formula in). The theory was proven incorrect as seen in the data below for anything with more than 30 days. We are curious about two items: (1) what is happening when we do a Date 1 minus Date 2, and (2) can we be certain that our days(date1) less days(date2) is the best course of action.
with dates (SomeDate) as (
SELECT CAST( '2002-09-16' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2010-10-18' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2010-12-30' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '1992-04-15' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '1998-02-04' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '1996-11-07' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2001-08-31' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2005-03-03' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2017-04-02' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2015-09-03' AS DATE) FROM SYSIBM.SYSDUMMY1)
select
SomeDate,
current date - SomeDate as Value1,
days(current date) - days(SomeDate)as DaysVal,
SomeDate + (current date - SomeDate) days as Check1,
SomeDate + (days(current date)- days(SomeDate)) days as Check2
from dates
| SOMEDATE | VALUE1 | DAYSVAL | CHECK1 | CHECK2 |
| 4/15/1992 | 250005 | 9136 | 10/11/2676 | 4/20/2017 |
| 11/7/1996 | 200513 | 7469 | 11/2/2545 | 4/20/2017 |
| 2/4/1998 | 190216 | 7015 | 11/21/2518 | 4/20/2017 |
| 8/31/2001 | 150720 | 5711 | 4/28/2414 | 4/20/2017 |
| 9/16/2002 | 140704 | 5330 | 12/11/2387 | 4/20/2017 |
| 3/3/2005 | 120117 | 4431 | 1/15/2334 | 4/20/2017 |
| 10/18/2010 | 60602 | 2376 | 9/19/2176 | 4/20/2017 |
| 12/30/2010 | 60321 | 2303 | 2/24/2176 | 4/20/2017 |
| 9/3/2015 | 10717 | 595 | 1/5/2045 | 4/20/2017 |
| 4/2/2017 | 18 | 18 | 4/20/2017 | 4/20/2017 |
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
April 20, 2017 at 11:27 am
Stop using the subtraction of one date from another, and compare your results with using the DATEDIFF function, which operates like this:
DATEDIFF(day, EarlierDateFieldValue, LaterDateFieldValue)
It might hold the key to seeing where the problem is coming from.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 20, 2017 at 11:28 am
Greg Snidow - Thursday, April 20, 2017 11:22 AMI was helping one of my new employees with a script that was being written to determine date value data. While trying to determine the number of days between the present and when an event occurred, the theory was that a simple Date 1 minus Date 2 would provide the requested answer (simple mathematical formula in). The theory was proven incorrect as seen in the data below for anything with more than 30 days. We are curious about two items: (1) what is happening when we do a Date 1 minus Date 2, and (2) can we be certain that our days(date1) less days(date2) is the best course of action.
with dates (SomeDate) as (
SELECT CAST( '2002-09-16' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2010-10-18' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2010-12-30' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '1992-04-15' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '1998-02-04' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '1996-11-07' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2001-08-31' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2005-03-03' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2017-04-02' AS DATE) FROM SYSIBM.SYSDUMMY1 UNION
SELECT CAST( '2015-09-03' AS DATE) FROM SYSIBM.SYSDUMMY1)select
SomeDate,
current date - SomeDate as Value1,
days(current date) - days(SomeDate)as DaysVal,
SomeDate + (current date - SomeDate) days as Check1,
SomeDate + (days(current date)- days(SomeDate)) days as Check2
from dates
SOMEDATE VALUE1 DAYSVAL CHECK1 CHECK2 4/15/1992 250005 9136 10/11/2676 4/20/2017 11/7/1996 200513 7469 11/2/2545 4/20/2017 2/4/1998 190216 7015 11/21/2518 4/20/2017 8/31/2001 150720 5711 4/28/2414 4/20/2017 9/16/2002 140704 5330 12/11/2387 4/20/2017 3/3/2005 120117 4431 1/15/2334 4/20/2017 10/18/2010 60602 2376 9/19/2176 4/20/2017 12/30/2010 60321 2303 2/24/2176 4/20/2017 9/3/2015 10717 595 1/5/2045 4/20/2017
4/2/2017 18 18 4/20/2017 4/20/2017
This seems to be specific functionality for DB2. This is a MS SQL Server site, so I can't give you much hope on getting the solution here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply