sqlite

Return year-month-first day of month from a date (per record)

Following a SQL book. Using SQLite.

Exercise is to return the 'Celebration Day'. First day of the month for each employees birthday.

Return the full date with the day always being '01'.

The following code runs, but does not produce the desired result.

SELECT
 FirstName ||' '|| LastName AS [Full Name],
 STRFTIME('%Y-%m-%d', BirthDate) AS [Birthday No Timecode],
 DATE(STRFTIME('%m'), 'start of month') AS [Celebrated on]
FROM
 employees;

This is the result:

Andrew Adams    1962-02-18  -4713-12-01
Nancy Edwards   1958-12-08  -4713-12-01
Jane Peacock    1973-08-29  -4713-12-01
Margaret Park   1947-09-19  -4713-12-01
Steve Johnson   1965-03-03  -4713-12-01
Michael Mitchell    1973-07-01  -4713-12-01
Robert King 1970-05-29  -4713-12-01
Laura Callahan  1968-01-09  -4713-12-01

I want the last column to be Year-Month-01 for all eight employees.

So, the first should return 1962-02-01, the next 1958-12-01, etc.

Trying to incorporate 'start of month' as noted in: https://www.sqlite.org/lang_datefunc.html

NOTE: I also tried: DATE(STRFTIME('%Y-%m'), 'start of month') AS [Celebrated on] but this returns Null for every date in the last column.

What I am trying to do is read the month in each employees birthdate, then use 'start-of-month' to get the first day of the month, then return the full Year-Month-01.

Either my syntax is bad or my intended technique is just flawed.

Solution

  • Either my syntax is bad or my intended technique is just flawed.

    You have a few flawed techniques:-

    The issues

    1. DATE(STRFTIME('%m'), 'start of month') and also DATE(STRFTIME('%Y-%m'), 'start of month') are not using any values from the table. Hence why the output for the Celebrated on is the same value for all rows.

    2. Furthermore, the date functions expect the datetime argument to conform to certain formats strftime('%m') equates to 11 (November) and would be acceptable for format 12 (see link below) which equates to the date -4713-12-05 with the 'start of month' modifier applied then -4713-12-01.

      1. i.e. with just the format (%m) for the strftime the omitted datetime value is now.
      2. when you use strftime('%Y-%m') then the result is 2024-11, this is then an invalid datetime value and hence the null. see https://www.sqlite.org/lang_datefunc.html#time_values

    A FIX (that uses the start of month modifier)

    What you probably want is to use (and perhaps meets the intent of your question) date(birthdate,'start of month'). This:-

    Uses date function with a time value as extracted from the birthdate column (which is in the valid format YYYY-MM-DD), modified by the start of month modifier.

    Demo

    The following demonstrates the above by replicating the issues, showing the suggested fix and shows the results broken down:-

    DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees (firstname,lastname,birthdate);
INSERT INTO employees VALUES
    ('Andrew','Adams','1962-02-18'),
    ('Nancy','Edwards','1958-12-08'),
    ('Jane','Peacock','1973-08-29'),
    ('Margaret','Park','1947-09-19'),
    ('Steve','Johnson','1965-03-03'),
    ('Michael','Mitchell','1973-07-01'),
    ('Robert','King','1970-05-29'),
    ('Laura','Callahan','1968-01-09')
;

SELECT
 FirstName ||' '|| LastName AS [Full Name],
 STRFTIME('%Y-%m-%d', BirthDate) AS [Birthday No Timecode], 
 DATE(STRFTIME('%m'), 'start of month') AS [Celebrated on], /* */
 DATE(STRFTIME('%Y-%m'), 'start of month') AS CO2, /* other attempt in question */
 date(birthdate,'start of month') AS CO3, /* SUGGESTED FIX using the start of month modifier*/
 strftime('%m') AS ex01, /* gets the current month */ 
 strftime('%Y-%m-%d') AS ex01a, /* demonstrates that without a timevalue then strftime defaults to now */
 date(11) AS ex02, /* 11 is a valid format BUT results in an unexpected date*/
 strftime('%Y-%m') AS ex03, /* results in 4 numeric current year, hyphen  and then the month */
 typeof(strftime('%Y-%m')) AS ex04, /* the result is TEXT type */
 date(strftime('%Y-%m')) AS ex05 /* as YYYY-MM IS NOT a valid date format then the date function resturns null */
FROM
 employees;

/* cleanup */
DROP TABLE IF EXISTS employees;

    Resulting in:-

    R1