Can you help with a date calculation? I'm on IBM DB2 if it helps (or hinders). I have a record with a date stored in a character field, formatted YYYYMMDD. How can I calculate when this date is less than 30 days from today? Please note, I can't (not allowed to) update the database using SQL, I'm only a lowly user.
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) )
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.