Requires Free Membership to View
Well, I certainly sympathize with your position. Of course, we all know that dates should not be stored in character fields, but this does happen, for any number of reasons, and then we have to work with it.
The main thing we need to do is convert the character value to a date value so that we can do date arithmetic on it. One way is to CAST it, another is to use the DATE function. Once we have a date value, we can compare it to CURRENT DATE like this:
select rowpk, chardatefield
from yourtable
where abs( days( date(chardatefield) )
- days( current date )
) < 30
Note that the DATE function, which converts an expression to a date value, requires a "valid string representation of a date." Despite a good half hour of searching on the IBM site, I was unable to find out exactly which string representations are considered valid. I saw a few examples (and they were few, and hard to find) that suggested that 'yyyy-mm-dd' was a valid string representation, but none that showed 'yyyymmdd'. (Note: even though I was frustrated in this instance, I hasten to point out that my impression of IBM's online documentation is otherwise very good.) Since I don't have access to DB2 and cannot test this myself, I'm going to go out on a limb and say that 'yyyymmdd' probably is a valid string representation of a date, so we can simply say DATE(chardatefield) and not worry about using substrings to pull out the year, month, and day in order to stuff dashes between them.
The second part of the solution involves converting dates to a day number using the DAYS function, which generates a result representing the number of days since January 1, 0000, or something. Note that the actual base date doesn't matter, nor do Julian and Gregorian calendar differences, because we are not concerned about the actual number of days, just their difference. So all that matters is that the day numbers are both calculated relative to the same base date.
The final part of the solution hinges on the fact that you did not specify whether you wanted to select dates less than 30 days from today in the past, in the future, or both. Since the subtraction of day numbers can result in positive or negative numbers depending on whether the date is in the past or future, the use of the ABS function selects dates which are within 30 days in either direction.
Addendum April 18, 2003
A kind reader provided a link
to the DB2 Universal Database for iSeries SQL Reference
manual, a 4.5meg PDF file on the IBM site. Fortunately,
I was able to find the HTML manual as well. See
String Representations of Datetime Values.
YYYYMMDD is not among the valid values, so in the solution above,
change DATE(chardatefield) to:
DATE(SUBSTR(chardatefield,1,4)||'-'||
SUBSTR(chardatefield,5,2)||'-'||
SUBSTR(chardatefield,7,2)
)
This was first published in April 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation