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.
Either my syntax is bad or my intended technique is just flawed.
You have a few flawed techniques:-
The issues
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.
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
.
%m
) for the strftime
the omitted datetime value is now
.2024-11
, this is then an invalid datetime value and hence the null. see https://www.sqlite.org/lang_datefunc.html#time_valuesA 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:-