SQL Date Manipulation

NOTE: Do NOT use 2 digit years. Whilst the RR date format may have provided a handy workaround when we reached the year 2000, a similar problem will rear its head again as we reach 2050 e.g. in 2049 a DOB of 01-Jan-51 implies a DOB of 01-Jan-1951 if we use the RR mask or 01-Jan-2051 (a date in the future!) if we use YY. In 2051 a DOB of 01-Jan-51 implies a DOB of 01-Jan-2051 if we use the RR mask and the same if we use YY. i..e. given that people can live to 100 the use of RR or YY is ambiguous. If the DOB were for a cat and the data were purged every 40 years then, as long as the 38 year record for the oldest cat were not beaten, a 2 digit year might work. But it's an unnecessary risk; the cost of storing those extra 2 characters is not enough to be significant. Use a 4 digit year and don't worry about it until we get near the year 9999.

The DATE datatype in Oracle RDBMS stores dates in Julian format internally. This means that the maximum valid date in a DATE column would be 31-Dec-4712.

Oracle

MySQL

MS-SQL

Postgres

Examples

Truncate to Day

Expected Result:

2023-03-03

Oracle

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

To override the default date display format

SELECT TRUNC(SYSDATE) FROM dual;

SELECT TRUNC(SYSDATE, 'DD') FROM dual;

MySQL

SELECT CURRENT_DATE;

SELECT DATE(SYSDATE());

First Day of Month

Expected Result:

2023-03-01

Oracle

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

To override the default date display format

SELECT TRUNC(SYSDATE, 'MM') FROM dual;

MySQL

SELECT DATE_FORMAT(CURRENT_DATE,'%Y-%m-01');

SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-01');

Yesterday

Expected Result:

2023-03-02

MySQL

SELECT DATE_FORMAT(SUBDATE(CURRENT_DATE, 1),'%Y-%m-01');

SELECT DATE_FORMAT(SUBDATE(SYSDATE(), 1),'%Y-%m-01');

UNIXTIME (Epoch)

PostgreSQL, where Epoch is stored in a "character varying" datatype called epoch_value...

SELECT epoch_value AS epoch,

       TO_TIMESTAMP(CAST(epoch_value AS BIGINT)/1000) AS datetime

  FROM mytable;

"epoch" "datetime""1607418198157" "2020-12-08 09:03:18+00""1695292608725" "2023-09-21 10:36:48+00"

Bibliography