The SQL Date Functions
SQL Date Functions provide a range of date functions that allow you to manipulate (according to you) date values in your database.
SQL Date Functions can perform various tasks, such as extracting parts of dates, formatting dates, adding or subtracting time intervals, and many more.
When working with dates, the hardest part is making sure that the format of the date you want to add fits the style of the database column that stores dates.
Here are some useful SQL date functions, along with examples:
SQL Date Functions in Oracle with Examples
CURRENT_DATE:
Returns the current date in the session’s time zone.
SELECT CURRENT_DATE FROM dual;
Output:
CURRENT_DATE
2023-12-10
SYSDATE:
We are finding the sysdate (System Date) of the operating system in where the database is installed.
Returns the current date and time in the database’s timezone.
SELECT SYSDATE FROM dual;
Output:
SYSDATE
2023-12-10 10:30:45
TO_DATE:
Convert a string to a date value with a specified format.
SELECT TO_DATE(‘2023/12/10’, ‘YYYY/MM/DD’) FROM dual;
Output:
TO_DATE(‘2023/12/10′,’YYYY/MM/DD’)
2023-12-10
EXTRACT:
Retrieves a specific component (year, month, day, hour, minute, etc.) from a date.
SELECT EXTRACT(YEAR FROM SYSDATE) AS YEAR,
EXTRACT(MONTH FROM SYSDATE) AS MONTH,
EXTRACT(DAY FROM SYSDATE) AS DAY
FROM dual;
Output:
| YEAR | MONTH | DAY |
|---|---|---|
| 2023 | 12 | 10 |
ADD_MONTHS:
Adds a specified number of months to a date.
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;
Output:
ADD_MONTHS(SYSDATE,3)
2024-03-10 10:30:45
LAST_DAY:
Returns the last day of the month for a given date.
SELECT LAST_DAY(SYSDATE) FROM dual;
Output:
LAST_DAY(SYSDATE)
2023-12-31
MONTHS_BETWEEN:
Calculates the number of months between two dates.
SELECT MONTHS_BETWEEN(‘2023/12/10’, ‘2023/10/10’) FROM dual;
Output:
MONTHS_BETWEEN(‘2023/12/10′,’2023/10/10’)
2
TRUNC:
Truncates a date to a specific level of precision (year, month, day, etc.).
SELECT TRUNC(SYSDATE, ‘MONTH’) FROM dual;
Output:
TRUNC(SYSDATE,’MONTH’)
2023-12-01
WHERE Cluase:
We use the WHERE clause with the SELECT statement:
Now, we can select the records with the SaleDate column of “2023-08-10” from the table.
SELECT * FROM Orders WHERE SaleDate=’2023-08-10′;
These functions offer flexibility in handling date and time operations in SQL, allowing users to extract, manipulate, and manage date values efficiently within their databases.
SQL Date Data Type in MYSQL:
MySQL comes with data types for storing date and time values in the database.
Syntax of MYSQL:
DATE format: YYYY-MM-DD
DATETIME format: YYYY-MM-DD HH:MI:SS
TIMESTAMP format: YYYY-MM-DD HH:MI:SS
YEAR format: YYYY or YY
SQL Date Data Type in SQL Server:
SQL Server comes with a data type for storing date and time values in the database.
DATE format: YYYY-MM-DD
DATETIME format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME format: YYYY-MM-DD HH:MI:SS
TIMESTAMP format: a unique number format
You can choose the date data type for a column while you create a new table in the database.
POSTGRESQL DATE FUNCTIONS:
These are the most popular PostgreSQL date functions that let you change date and time numbers in a useful way. Please see the below syntax with examples.
PostgreSQL AGE Function:
Two TIMESTAMP numbers can be passed to the AGE() method. It takes the second reason away from the first one and gives back an interval.
AGE(timestamp,timestamp);
SELECT AGE(‘2023-01-11′,’2013-06-11’);
PostgreSQL NOW() Function:
The NOW() method gives you the date and time right now. When you call NOW(), it will return a timestamp with the time zone.
SELECT NOW();
SELECT NOW()::timestamp;
SELECT now() – interval ‘5 hours 30 minutes’ AS two_hour_30_min_go;
PostgreSQL CURRENT_DATE Function:
The CURRENT_DATE function gives you back a DATE number that shows what day it is right now.
CURRENT_DATE
SELECT CURRENT_DATE;
PostgreSQL CURRENT_TIME Function:
The CURRENT_TIME function gives you back a TIME WITH TIME ZONE number that shows what time it is right now in your time zone.
CURRENT_TIME(precision)
SELECT CURRENT_TIME;
What is precision?
The precision option tells the calculator how precise to return the fractional seconds. If you don’t give the precision argument, the full precision will be used in the answer.
PostgreSQL CURRENT_TIMESTAMP Function:
The CURRENT_TIMESTAMP() method in PostgreSQL gives you the current date and time, along with the time zone. This is the time when the transaction begins. You can get different values from the CURRENT_TIMESTAMP() function in a number of different ways like CENTURY, DAY, DECADE, DOW, DOY, EPOCH, HOUR, MICROSECONDS, MILLENNIUM, and MILLISECONDS etc.
Syntax
The syntax of the PostgreSQL CURRENT_TIMESTAMP() function:
CURRENT_TIMESTAMP(precision)
SELECT CURRENT_TIMESTAMP;
PostgreSQL EXTRACT Function:
The PostgreSQL EXTRACT() function method takes a date and time number and gets a field from it, like the year, month, and day.
Syntax
The syntax with examples of the EXTRACT() function:
EXTRACT year from a timestamp:
To extract year from a timestamp:
SELECT EXTRACT(YEAR FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract month from a timestamp:
SELECT EXTRACT(MONTH FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract day from a timestamp:
SELECT EXTRACT(DAY FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract century from a timestamp:
SELECT EXTRACT(CENTURY FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract decade from a timestamp:
SELECT EXTRACT(DECADE FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract the day of week from a timestamp:
SELECT EXTRACT(DOW FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract the day of year from a timestamp:
SELECT EXTRACT(DOY FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract the epoch from a timestamp:
SELECT EXTRACT(EPOCH FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract hour from a timestamp:
SELECT EXTRACT(HOUR FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract the minute from a timestamp:
SELECT EXTRACT(MINUTE FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract second from a timestamp:
SELECT EXTRACT(SECOND FROM TIMESTAMP ‘2022-12-31 12:30:45.45’);
To extract the weekday according to ISO:
SELECT EXTRACT(ISODOW FROM TIMESTAMP ‘2022-12-31 12:30:45’);
To extract the millisecond from a timestamp:
SELECT EXTRACT(MILLISECONDS FROM TIMESTAMP ‘2022-12-31 12:30:45’);
PostgreSQL LOCALTIME Function:
The LOCALTIME function in PostgreSQL gives you the time when the current transaction began.
Syntax
The syntax of the LOCALTIME function:
LOCALTIME(precision)
SELECT LOCALTIME;
PostgreSQL LOCALTIMESTAMP Function:
There is a PostgreSQL method called LOCALTIMESTAMP that gives you the current date and time of the transaction.
Syntax
The syntax of the LOCALTIMESTAMP function:
LOCALTIMESTAMP(precision)
SELECT LOCALTIMESTAMP;
PostgreSQL DATE_PART Function:
The DATE_PART() function takes a date or time number and pulls out a part. This shows how the DATE_PART() method works.
DATE_PART(field,source)
SELECT date_part(‘century’,TIMESTAMP ‘2020-01-01’);
PostgreSQL DATE_TRUNC Function:
When you tell the date_trunc function what part of the date to truncate, like an hour, week, or month, it will return a TIMESTAMP or INTERVAL number.
The syntax of the date_trunc function:
date_trunc(‘datepart’, field)
SELECT DATE_TRUNC(‘hour’, TIMESTAMP ‘2020-01-01 02:09:30’);
PostgreSQL TO_DATE Function:
The TO_DATE() function takes a string text and turns it into a date value. This is how the TO_DATE() code is written:
TO_DATE(text,format);
SELECT TO_DATE(‘20200101′,’YYYYMMDD’);
PostgreSQL TO_TIMESTAMP Function:
The TO_TIMESTAMP() code takes a string and turns it into a timestamp in the format that was given.
TO_TIMESTAMP(timestamp, format)
SELECT TO_TIMESTAMP(
‘2020-01-01 10:30:30’,
‘YYYY-MM-DD HH:MI:SS’
);
NOTE:
- The CURRENT_TIMESTAMP function and the TRANSACTION_TIMESTAMP() function are the same thing in PostgreSQL. But it’s clear from the name of the function TRANSACTION_TIMESTAMP what it returns.
- The CURRENT_TIME function gives you a TIME value with time zone, while the LOCATIME function gives you a TIME value without time zone.
- The CURRENT_TIMESTAMP function gives you a TIMESTAMP value with time zone, while the LOCALTIMESTAMP function gives you a TIMESTAMP value without time zone.
- The PostgreSQL TO_TIMESTAMP() method gives you a timestamp that includes the time zone.