Frequently Used DB2 Functions and Expressions

Get the number of days since 0001-01-01

SELECT DAYS('2016-12-25')

Convert date string to datetime type

SELECT date(to_date(string_date_column,'YYYY-MM-DD'))

Calculate difference in number of days between two dates

SELECT DAYS('2016-01-01') - DAYS('2015-01-01')

Get day of the month, month, and year

SELECT DAY('2016-12-25'), MONTH('2016-12-25'), YEAR('2016-12-25')

From DB2 v10, dates can be formatted with a desired format string

SELECT VARCHAR_FORMAT(date-column, 'YYYY-MM-DD HH:MI:SS')

Frequent Regular Expressions

Here are some frequently used regular expressions.

IP Address:
^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$

Social Security Number:
^\d{3}-?\d{2}-?\d{4}$

Credit Card: [Visa, MC, AmEx, and Discover]
^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|(?:2131|1800|35\d{3})\d{11})$

Use Luhn algorithm to check if a credit card number is a valid number. Of course, the algorithm doesn't obviously check if the card is actually any good. Such must be checked with the card issuer.