Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Birthdays
The following are some simple examples for handling date-related queries. Assume that you have an example table called Datetests, which contains a DATE column called datefield. We will use pure SQL Standard syntax.
List people whose birthdays come within the next 14 days:
SELECT name, datefield FROM Datetests WHERE (CURRENT_DATE – (datefield - INTERVAL '14' DAY)) YEAR <> (CURRENT_DATE - datefield) YEAR;
This will fail if any datefield value is greater than CURRENT_DATE.
Return the current millennium of Christ's era:
SELECT SUBSTRING (CAST (EXTRACT (YEAR FROM CURRENT_DATE) AS CHAR(4)) FROM 1 FOR 1) FROM Datetests;This works and avoids the MOD operator, which requires SQL:1999 support. It also returns 2 if the year is 2000, which is the only correct answer.
List all rows and say Happy Birthday where appropriate:
SELECT datefield, CASE WHEN (datefield + INTERVAL '1' DAY) - CURRENT_DATE) YEAR <> (datefield - CURRENT_DATE) YEAR THEN 'Happy Birthday' ELSE ' ' END FROM Datetests;
This is technically more accurate than using EXTRACT to compare month and day because it gives people born on February 29 a birthday on March 1 in non-leap years. We think that is the ordinary convention.