The following SQL is an example of how to convert a date such as 17041967 to 170467 create table myfile as ( SELECT ACCOUNTNR, datedmcy INTEGER((substr(digits(datedmcy), 1, 2) CONCAT substr(digits(datedmcy), 3, 2) CONCAT substr(digits(datedmcy), 7, 2))) as datedmy FROM account ) with data

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 […]

SQL Convert String to Upper Case postcode: cf64 3pg update customer set postcode = UPPER(postcode) postcode: CF64 3PG SQL Convert String to Lower Case postcode: CF64 3PG update customer set postcode = LOWER(postcode) postcode: cf64 3pg

SQL is very useful for data hygiene projects allowing analysts to check the validity and integrity of the database. However, checking that a legacy date is correct cannot be done without the use of a user-defined function. The following function accepts a numeric date in the format ddmmyy. This is converted into standard date format and validated. The function returns a […]