Many older databases have the dates held in numeric fields in formats such as ddmmyy. This can be problematic when performing selection and calculations. However, by using the functions DATE, SUBSTR, DIGITS & CONCAT we are able to carry out these SQL selects.

The following SQL statement will select all accounts with a due date of tomorrow.

SELECT accountnr , duedate , current date
FROM   prodlib/accounts
WHERE  duedate <> 0 and
       DATE(substr(digits(duedate), 1, 2) CONCAT '/' CONCAT
       substr(digits(duedate), 3, 2) CONCAT '/' CONCAT
       substr(digits(duedate), 5, 2))
       =  current date + 1  DAYS

Notes:

1. The value current date is today’s date. It can also be used in isolation to display today’s date using VALUES current date
2. The field duedate contains a “date” in the format ddmmyy such as 250518.
3. The next part is the sql server convert string to date for which we use the DATE function.  However, this expects a string in the format ‘dd/mm/yy’, and this is why we use a combination of SUBSTR, DIGITS & CONCAT.  Our “date” field is numeric so we use DIGITS to convert to a string. We can then perform a sub-string command to extract each element and insert the date separator with the CONCAT functions.
4. The element “+ 1 DAYS” performs some basic date maths on our current date so that we check for dates equal to tomorrow. We could of course apply “- 1 DAYS” to our calculated date and achieve the same result.

SELECT accountnr , duedate , current date
FROM   prodlib/accounts
WHERE  duedate <> 0 and
       DATE(substr(digits(duedate), 1, 2) CONCAT '/' CONCAT
       substr(digits(duedate), 3, 2) CONCAT '/' CONCAT
       substr(digits(duedate), 5, 2)) - 1  DAYS
       =  current date

Results:

....+....1....+....2....+....3....+....4....+...
Account   Due date CURRENT DATE
 00018860 120,518 11/05/18 
 00023217 120,518 11/05/18 
 00035274 120,518 11/05/18 
 00040746 120,518 11/05/18 
 00045819 120,518 11/05/18 
 00067961 120,518 11/05/18