Handling Dates Part 3

SQL provides several useful functions to manipulate and format dates. Here are some common date functions:

1. DATE() - Converts a string to a date
2. STRFTIME() - Formats dates according to specified parameters
3. DATE('now') - Gets current date:

For example convert string to date:

SELECT DATE('2023-05-15 13:45:00')
-- Returns: 2023-05-15

SELECT DATE('now')
-- Returns current date

For example extract year, month and day from date:

-- Get year
SELECT STRFTIME('%Y', '2023-05-15')
-- Returns: 2023

-- Get month
SELECT STRFTIME('%m', '2023-05-15')
-- Returns: 05

-- Get day
SELECT STRFTIME('%d', '2023-05-15')
-- Returns: 15

-- Show combination of year and day (any separator can be used)
SELECT STRFTIME('%Y:%d', '2023-05-15')
-- Returns: 2023:15

-- Use a dash separator instead
SELECT STRFTIME('%d-%m-%Y', '2023-05-15')
-- Returns: 15-05-2023

-- Get day of week
SELECT STRFTIME('%w', '2023-05-15')
-- Returns: 1 (Monday; Sunday is 0)

Common format specifiers:

• %Y: Year (4 digits)
• %m: Month (01-12)
• %d: Day of month (01-31)
• %w: Day of week (0-6, Sunday is 0)
• %H: Hour (00-23)
• %M: Minute (00-59)
• %S: Second (00-59)

You can use any separator character between specifiers (e.g. -, :, /). For example, STRFTIME('%d-%m-%Y', date) and STRFTIME('%d:%m:%Y', date) both combine day, month, and year — just with different separators.

You can combine these functions in queries:

-- Find all records from current year
SELECT * FROM table1
WHERE STRFTIME('%Y', date_column) = STRFTIME('%Y', 'now')