Home > Articles > Data

  • Print
  • + Share This
From the author of

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.

  1. 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.

  2. 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.

  3. 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.

  • + Share This
  • 🔖 Save To Your Account