Guida alla formattazione delle date in SQL

strftime(format, date)

SELECT strftime('%m/%d/%Y', '2026-03-15');
-- Output: 03/15/2026

SELECT strftime('%I:%M %p', '2026-03-15 14:30:00');
-- Output: 02:30 PM

-- Input: orders
-- order_id | order_date
-- ---------+--------------------
-- 1        | 2026-01-15 09:42:00
-- 2        | 2026-03-02 16:05:00

SELECT
  order_date,
  strftime('%Y-%m-%d', order_date) AS iso_date,
  strftime('%m/%d/%Y', order_date) AS us_date,
  strftime('%d/%m/%Y', order_date) AS european_date
FROM orders;

-- Output:
-- order_date          | iso_date   | us_date    | european_date
-- --------------------+------------+------------+--------------
-- 2026-01-15 09:42:00 | 2026-01-15 | 01/15/2026 | 15/01/2026
-- 2026-03-02 16:05:00 | 2026-03-02 | 03/02/2026 | 02/03/2026

-- Input: sales
-- id | sale_date  | amount
-- ---+------------+-------
-- 1  | 2025-11-20 | 89.0
-- 2  | 2026-01-08 | 120.0
-- 3  | 2026-03-04 | 64.5

-- Wrong: '11/2025' > '02/2026' is true, because '1' > '0'
SELECT * FROM sales
WHERE strftime('%m/%Y', sale_date) > '02/2026';

-- Output:
-- id | sale_date  | amount
-- ---+------------+-------
-- 1  | 2025-11-20 | 89.0
-- 3  | 2026-03-04 | 64.5

-- Right: put the year first
SELECT * FROM sales
WHERE strftime('%Y-%m', sale_date) > '2026-02';

-- Output:
-- id | sale_date  | amount
-- ---+------------+-------
-- 3  | 2026-03-04 | 64.5

-- PostgreSQL: TO_CHAR(value, pattern)
-- (FM stops it padding the day name to 9 characters)
SELECT TO_CHAR(TIMESTAMP '2026-01-15 09:42:00', 'FMDay, MM/DD/YYYY HH24:MI');
-- Output: Thursday, 01/15/2026 09:42

-- MySQL: DATE_FORMAT(value, pattern)
-- (%i is the minute; %M would be the month name)
SELECT DATE_FORMAT('2026-01-15 09:42:00', '%W, %m/%d/%Y %H:%i');
-- Output: Thursday, 01/15/2026 09:42

-- SQL Server: FORMAT(value, pattern)
-- (MM is the month, mm the minute)
SELECT FORMAT(CAST('2026-01-15 09:42:00' AS datetime2), 'dddd, MM/dd/yyyy HH:mm');
-- Output: Thursday, 01/15/2026 09:42

-- Older SQL Server code uses CONVERT with a style number (101 is mm/dd/yyyy)
SELECT CONVERT(VARCHAR(10), CAST('2026-01-15' AS date), 101);
-- Output: 01/15/2026

-- Input: audit_log
-- id | action | logged_at
-- ---+--------+--------------------
-- 1  | login  | 2026-03-15 08:05:12
-- 2  | export | 2026-03-15 14:30:45
-- 3  | logout | 2026-03-15 17:58:03

SELECT
  id,
  action,
  strftime('%Y-%m-%dT%H:%M:%SZ', logged_at) AS logged_at_iso
FROM audit_log;

-- Output:
-- id | action | logged_at_iso
-- ---+--------+---------------------
-- 1  | login  | 2026-03-15T08:05:12Z
-- 2  | export | 2026-03-15T14:30:45Z
-- 3  | logout | 2026-03-15T17:58:03Z

-- Input: users
-- id | name       | signup_date
-- ---+------------+------------
-- 1  | Ana Lima   | 2026-01-08
-- 2  | Ben Okafor | 2026-01-27
-- 3  | Chloe Park | 2026-02-14
-- 4  | Dev Mehta  | 2026-03-03
-- 5  | Elle Novak | 2026-03-30

SELECT
  TO_CHAR(signup_date, 'YYYY-MM') AS month,
  COUNT(*) AS signups
FROM users
GROUP BY TO_CHAR(signup_date, 'YYYY-MM')
ORDER BY month;

-- Output:
-- month   | signups
-- --------+--------
-- 2026-01 | 2
-- 2026-02 | 1
-- 2026-03 | 2

-- Input: bookings
-- guest_name  | booking_time
-- ------------+--------------------
-- Priya Patel | 2026-03-16 14:30:00
-- Liam Murphy | 2026-03-18 09:15:00

SELECT
  guest_name,
  FORMAT(booking_time, 'dddd, MMMM d') + ' at ' + FORMAT(booking_time, 'h:mm tt') AS confirmation
FROM bookings;

-- Output:
-- guest_name  | confirmation
-- ------------+-------------------------------
-- Priya Patel | Monday, March 16 at 2:30 PM
-- Liam Murphy | Wednesday, March 18 at 9:15 AM

-- Input: orders
-- order_id | order_date
-- ---------+--------------------
-- 1        | 2026-03-07 10:12:00
-- 2        | 2026-03-09 16:40:00
-- 3        | 2026-03-14 11:05:00
-- 4        | 2026-03-16 09:20:00
-- 5        | 2026-03-21 13:45:00

SELECT
  DATE_FORMAT(order_date, '%W') AS weekday,
  COUNT(*) AS order_count
FROM orders
GROUP BY weekday
ORDER BY order_count DESC;

-- Output:
-- weekday  | order_count
-- ---------+------------
-- Saturday | 3
-- Monday   | 2