Dates in SQL

SQL date format is YYYY-MM-DD HH:MM: SS

Dates can be in different formats because:

• Data manipulation in Excel MM/DD/YYYY

• Data was manually entered

• Data uses text to record months

Functions

SELECT DATE_TRUNC('day', date), SUM(total)
FROM supermarket_sales
GROUP BY DATE_TRUNC('day', date)
ORDER BY DATE_TRUNC('day', date)

DATE_TRUNC allows to truncate to a particular point. it is available in PostgreSQL, IBM Db2, and Google BigQuery but not in most other flavours.

DATE_PART month, dow(day of week)

EXTRACT Can be used to deconstruct a date. Extract year, month, day, hour or so.

The below SQL functions return the current date, time, and timestamp, respectively, based on the system clock.

SELECT CURRENT_DATE AS date,
       CURRENT_TIME AS time,
       CURRENT_TIMESTAMP AS timestamp,
       LOCALTIME AS localtime,
       LOCALTIMESTAMP AS localtimestamp,
       NOW() AS now

DateAdd

is used to add or subtract a specified interval (such as days, months, or years) to a date or timestamp.

-- Adding 7 days to a date
SELECT DATEADD(day, 7, your_date_column) AS new_date FROM your_table;

-- Subtracting 1 month from a timestamp
SELECT DATEADD(month, -1, your_timestamp_column) AS new_timestamp FROM your_table;

DatedIff

Calculates the difference between two dates or timestamps in terms of a specified interval (e.g., days, months, or years)

-- Calculating the number of days between two dates
SELECT DATEDIFF(day, start_date, end_date) AS day_difference FROM your_table;

-- Calculating the number of months between two timestamps
SELECT DATEDIFF(month, timestamp1, timestamp2) AS month_difference FROM your_table;

Date Format

is used to format dates or timestamps into a specific string format.

-- Formatting a date as 'YYYY-MM-DD'
SELECT DATE_FORMAT(your_date_column, '%Y-%m-%d') AS formatted_date FROM your_table;

-- Formatting a timestamp as 'MM/DD/YYYY HH:MI AM'
SELECT DATE_FORMAT(your_timestamp_column, '%m/%d/%Y %h:%i %p') AS formatted_timestamp FROM your_table;

In Postgresql and Oracle, one can use to_char to format dates.

Different date formats:

• %YYYY: Four-digit year (e.g., 2023).

• %YY: Last two digits of the year (e.g., 23).

• %MM: Month (01-12).

• %DD: Day of the month (01-31).

• %HH: Hour (00-23).

• %MI: Minute (00-59).

• %SS: Second (00-59).

• %AM or %PM: AM or PM for 12-hour time format.

• %Dy: Abbreviated day of the week (e.g., Mon).

• %Day: Full day of the week (e.g., Monday).

• %Mon: Abbreviated month (e.g., Jan).

• %Month: Full month (e.g., January).

SELECT TO_CHAR(your_date_column, 'YYYY-MM-DD') AS formatted_date FROM your_table;

SQL server uses CONVERT

• 101: USA date format (e.g., 'mm/dd/yyyy').

• 120: ODBC canonical (e.g., 'yyyy-mm-dd hh:mi:ss').

SELECT CONVERT(VARCHAR, your_date_column, 101) AS formatted_date FROM your_table;

Further reading

https://www.postgresql.org/docs/9.1/functions-datetime.html