|
View previous topic :: View next topic
|
| Author |
Message |
jm_green84
New User
Joined: 13 Dec 2006
Posts: 11
Location: ATL
|
|
|
|
How would I write a query to see if "one date is 1 day after another date?
Title changed from "query help" to "query to see if one date is 1 day after another date?" : Priyesh. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Please post some sample data and what you'd like to have returned in your query. |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Maybe you can adapt this to your need
| Code: |
SELECT DT.DATE1,
DT.DATE2,
CAST(DAY(DATE(DT.DATE1) - DATE(DT.DATE2)) AS DEC(5)) AS DAYS,
CASE
WHEN DAY(DATE(DT.DATE1) - DATE(DT.DATE2)) = 1
THEN 'Y'
WHEN DAY(DATE(DT.DATE1) - DATE(DT.DATE2)) = -1
THEN 'Y'
ELSE 'N'
END AS ONE_DAY
FROM
(SELECT '2006-12-12' AS DATE1,
'2006-12-13' AS DATE2
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2006-12-12' AS DATE1,
'2006-12-12' AS DATE2
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2006-12-13' AS DATE1,
'2006-12-12' AS DATE2
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2007-01-01' AS DATE1,
'2006-12-31' AS DATE2
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2007-01-01' AS DATE1,
'2007-01-03' AS DATE2
FROM SYSIBM.SYSDUMMY1
) DT
|
Result
| Code: |
+----------------------------------------------+
| DATE1 | DATE2 | DAYS | ONE_DAY |
+----------------------------------------------+
1_| 2006-12-12 | 2006-12-13 | -1 | Y |
2_| 2006-12-12 | 2006-12-12 | 0 | N |
3_| 2006-12-13 | 2006-12-12 | 1 | Y |
4_| 2007-01-01 | 2006-12-31 | 1 | Y |
5_| 2007-01-01 | 2007-01-03 | -2 | N |
+----------------------------------------------+
|
|
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| jm_green84 wrote: |
| How would I write a query to see if "one date is 1 day after another date? |
| Quote: |
| The DAYS function returns an integer representation of a date. |
Assuming that the "dates" are in a suitable "date" format, subtract and compare.... |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
How about keeping it a little simpler... 
| Code: |
CASE
WHEN DATE1 + 1 DAY = DATE2
THEN 'Y'
WHEN DATE2 + 1 DAY = DATE1
THEN 'Y'
ELSE 'N'
END AS ONE_DAY |
I hope syntax is correct... I m bad at that...  |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
priyesh,
This will work. He may have to 'CAST' date1 and date2 (if declared as CHAR(10)) to DATE datatype. |
|
| Back to top |
|
|
|
|
