SQL Date Manipulation

Coding

SQL

Oracle

MySQL

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.

SQL Current Date/Time

SQL Date Maths

SQL Date Comparison

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

SYSDATE
TRUNC

SYSDATE()
DATE
DATE_FORMAT
SUBDATE

CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

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

https://www.quora.com/Why-is-Oracle-Applications-end-date-31-Dec-4712-What-is-so-specific-special-about-this-datehttps://www.w3schools.com/sqL/func_sqlserver_current_timestamp.asphttps://www.timeanddate.com/calendar/gregorian-calendar.htmlhttps://www.unixtimestamp.com/
Oraclehttps://www.sqlines.com/oracle-to-mysql/trunc_datetime
MySQLhttps://www.sqlines.com/oracle-to-mysql/trunc_datetimehttps://stackoverflow.com/questions/7146828/mysql-selecting-yesterdays-date
PostgreSQLhttps://stackoverflow.com/questions/24751372/no-function-matches-the-given-name-and-argument-typeshttps://stackoverflow.com/questions/16609722/postgresql-how-to-convert-from-unix-epoch-to-datehttps://www.postgresqltutorial.com/postgresql-string-functions/postgresql-to_char/

Page updated

Google Sites

Report abuse