Date Functions in SQL

Date Functions in SQL

Date functions in SQL are used to work with date and time values, enabling you to perform operations such as calculations, formatting, and extraction. The specific functions and their syntax can vary between SQL database systems, but the following are commonly supported functions.

CURRENT_DATE / GETDATE() / SYSDATE

• CURRENT_DATE: Returns the current date (ISO format). This function is available in many SQL systems, including PostgreSQL and MySQL.
• GETDATE(): Returns the current date and time. This function is used in SQL Server.
• SYSDATE: Returns the current date and time. This function is used in Oracle.

Example 

SELECT CURRENT_DATE AS today_date;

Returns the current date. 

SELECT GETDATE() AS current_datetime;

Returns the current date and time.

DATEADD

• DATEADD(datepart, number, date): Adds a specified number of time units (days, months, years, etc.) to a date. This function is available in SQL Server.

Example 

SELECT DATEADD(day, 10, CURRENT_DATE) AS future_date;

Adds 10 days to the current date.

DATEDIFF

• DATEDIFF(datepart, start_date, end_date): Returns the difference between two dates in the specified time units (days, months, years, etc.). This function is available in SQL Server.

Example 

SELECT DATEDIFF(day, '2024-01-01', CURRENT_DATE) AS days_difference;

Calculates the number of days between January 1, 2024, and today.

DATEPART

• DATEPART(datepart, date): Extracts a specified part of a date (e.g., year, month, day). This function is available in SQL Server.

Example 

SELECT DATEPART(year, CURRENT_DATE) AS current_year,
       DATEPART(month, CURRENT_DATE) AS current_month;

Extracts the year and month from the current date.

EXTRACT

• EXTRACT(field FROM date): Extracts a specific part of a date (e.g., year, month, day). This function is available in PostgreSQL, MySQL, and Oracle.

Example 

SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS current_year,
       EXTRACT(MONTH FROM CURRENT_DATE) AS current_month;

Extracts the year and month from the current date.

TO_DATE / STR_TO_DATE

• TO_DATE(date_string, format): Converts a string to a date using a specified format. This function is used in Oracle.
• STR_TO_DATE(date_string, format): Converts a string to a date using a specified format. This function is used in MySQL.

Example 

SELECT TO_DATE('25-08-2024', 'DD-MM-YYYY') AS formatted_date;

Converts the string ’25-08-2024′ to a date. 

SELECT STR_TO_DATE('2024/08/25', '%Y/%m/%d') AS formatted_date;

 Converts the string ‘2024/08/25’ to a date.

TO_CHAR / DATE_FORMAT

• TO_CHAR(date, format): Converts a date to a string using a specified format. This function is used in Oracle.
• DATE_FORMAT(date, format): Converts a date to a string using a specified format. This function is used in MySQL.

Example 

SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS formatted_date;

Formats the current date as ‘YYYY-MM-DD’. 

SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d') AS formatted_date;

 Formats the current date as ‘YYYY-MM-DD’.

NOW()

• NOW(): Returns the current date and time. This function is used in MySQL and PostgreSQL.

Example 

SELECT NOW() AS current_datetime;

Returns the current date and time.

LAST_DAY

• LAST_DAY(date): Returns the last day of the month for a given date. This function is used in Oracle.

Example 

SELECT LAST_DAY(CURRENT_DATE) AS last_day_of_month;

Returns the last day of the current month.

MONTHS_BETWEEN

• MONTHS_BETWEEN(date1, date2): Returns the number of months between two dates. This function is used in Oracle.

Example 

SELECT MONTHS_BETWEEN(CURRENT_DATE, '2023-01-01') AS months_difference;

Calculates the number of months between January 1, 2023, and today.

DATE_TRUNC

• DATE_TRUNC(field, date): Truncates a date to a specified precision (e.g., year, month). This function is available in PostgreSQL.

Example

Returns the first day of the current month.

Summary

Date functions in SQL are essential for manipulating and formatting date and time values. They enable you to perform operations such as calculating intervals, extracting specific parts of a date, and converting dates to various formats. Understanding these functions and how to use them effectively will help you manage temporal data in your SQL queries. Note that the exact names and availability of these functions may vary depending on the SQL database system you are using.