| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
03-30-11, 10:15
|
|
Registered User
|
|
Join Date: Mar 2011
Posts: 4
|
|
|
Help needed with date compare
|
|
Hi folks,
I'm baffled with this one. I am selecting records based on a date comparison using the ISO standard yyyy-mm-dd . I'm getting a date is not in the acceptable range error only for the second date line. If I remove it, the query runs fine. This only occurs for certain records - when I examine them , they look fine. Any help would be appreciated.
select a.acct#
from ph#file.phpacct a
where a.amdrc = '33-86-09'
and date(a.aamcy || '-' || a.aammm || '-' || a.aamdd) <= '2010-01-03'
and date(a.adscy || '-' || a.adsmm || '-' || a.adsdd) >= '2011-01-03'
|
|
03-30-11, 10:41
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
Why don't you store dates as DATE values instead of separating them into 3 columns?
Can you show us he rows for which the comparison fails? Maybe you have an invalid month/day number in there like February 30th or so.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
03-30-11, 11:10
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
What are data types of adscy, adsmm and adsdd?
If each of them were CHAR(4), CHAR(2) and CHAR(2), did you included leading zero?
For example:
This(leading zero was included) will be OK. adscy = '2011', adsmm = '01' and adsdd = '03'
This(left adjust) may be error. adscy = '2011', adsmm = '1 ' and adsdd = '3 '
This(right adjust) may be error. adscy = '2011', adsmm = ' 1' and adsdd = ' 3'
This(left adjust, but month was 10, 11 or 12) will be OK. adscy = '2011', adsmm = '12' and adsdd = '3 '
|
|
03-30-11, 12:22
|
|
Registered User
|
|
Join Date: Mar 2011
Posts: 4
|
|
|
Date compare
Thank you for your replies - Unfortunately, this is how they currently store the dates in separate columns :-(
Also, I have tries using leading zero's etc to no avail... still looking...
|
|
03-30-11, 12:32
|
|
Registered User
|
|
Join Date: May 2009
Posts: 472
|
|
pligi, You appear to have a value out of range.
Check that ADSCY is between 1 and 9999.
Check that ADSMM is between 1 and 12
Check that ADSDD is between 1 and 31.
You probably have a Day value that is out of range for the Month it is in.
I would check for any ADSDD values = 31 where the ADSMM value is in 2, 4, 6, 9, 11. Also for any ADSMM value = 2 and ADSDD values = 29, 30, 31 and find out if any with 29 is a leap year.
|
|
03-30-11, 14:00
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
How about this query to find invalid adsdd or aamdd?
Code:
SELECT a.acct#
, a.adscy , a.adsmm , a.adsdd
, CASE
WHEN a.adsdd > SUBSTR( CHAR( DATE(a.adscy || '-' || a.adsmm || '-01')
+ 1 MONTH - 1 DAY , ISO )
, 9 , 2 )
THEN 'adsdd is out of range.'
ELSE ''
END AS invalid_adsdd
, a.aamcy , a.aammm , a.aamdd
, CASE
WHEN a.aamdd > SUBSTR( CHAR( DATE(a.aamcy || '-' || a.aammm || '-01')
+ 1 MONTH - 1 DAY , ISO )
, 9 , 2 )
THEN 'aamdd is out of range.'
ELSE ''
END AS invalid_aamdd
FROM ph#file.phpacct a
WHERE a.amdrc = '33-86-09'
AND ( a.adsmm IN ('02' , '04' , '06' , '09' , '11')
AND
a.adsdd > SUBSTR( CHAR( DATE(a.adscy || '-' || a.adsmm || '-01')
+ 1 MONTH - 1 DAY , ISO )
, 9 , 2 )
OR a.aammm IN ('02' , '04' , '06' , '09' , '11')
AND
a.aamdd > SUBSTR( CHAR( DATE(a.aamcy || '-' || a.aammm || '-01')
+ 1 MONTH - 1 DAY , ISO )
, 9 , 2 )
)
Or more generally...
Code:
SELECT a.acct#
, a.adscy , a.adsmm , a.adsdd
, CASE
WHEN a.adsdd NOT BETWEEN
'01'
AND SUBSTR( CHAR( DATE(a.adscy || '-' || a.adsmm || '-01')
+ 1 MONTH - 1 DAY , ISO )
, 9 , 2 )
THEN 'adsdd is out of range.'
ELSE ''
END AS invalid_adsdd
, a.aamcy , a.aammm , a.aamdd
, CASE
WHEN a.aamdd NOT BETWEEN
'01'
AND SUBSTR( CHAR( DATE(a.aamcy || '-' || a.aammm || '-01')
+ 1 MONTH - 1 DAY , ISO )
, 9 , 2 )
THEN 'aamdd is out of range.'
ELSE ''
END AS invalid_aamdd
FROM ph#file.phpacct a
WHERE a.amdrc = '33-86-09'
AND ( a.adsdd NOT BETWEEN
'01'
AND SUBSTR( CHAR( DATE(a.adscy || '-' || a.adsmm || '-01')
+ 1 MONTH - 1 DAY , ISO )
, 9 , 2 )
OR a.aamdd NOT BETWEEN
'01'
AND SUBSTR( CHAR( DATE(a.aamcy || '-' || a.aammm || '-01')
+ 1 MONTH - 1 DAY , ISO )
, 9 , 2 )
)
|
Last edited by tonkuma; 03-30-11 at 14:15.
Reason: Add a.adscy , a.adsmm , a.adsdd , a.aamcy , a.aammm , a.aamdd to the results.
|
03-30-11, 15:36
|
|
Registered User
|
|
Join Date: Mar 2011
Posts: 4
|
|
|
Getting same error
Hmm... Tonkuma, thank you for the code but I am getting the same error as before- out of range. Wonder if there is some corruption going on here.
|
|
03-30-11, 15:45
|
|
Registered User
|
|
Join Date: Mar 2011
Posts: 4
|
|
|
Found it!!
Problem was caused by bad data - some records contained zeros in the date columns. When I exclude this data, the query works fine. Thanx to all who replied, it was appreciated!!
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help needed with date compare
