SQL Date & Time Functions:

Level Up Your SQL Skills:

SQL Date & Time Functions play a crucial role in handling date and time data in databases. They enable us to effectively extract, manipulate, and format dates and times according to our requirements.

Photo from: Pexels

In this article, we will explore the most commonly used SQL Date & Time Functions and provide examples to demonstrate their usage. By mastering these functions, you’ll be equipped to handle date and time data effectively in your SQL queries and gain valuable insights from your datasets.

1. DATE( )

It extracts the date portion from a date/time value.

Example:
SELECT DATE(‘column_name’)
FROM table_name;

-- Extract the date from hire_date column.
SELECT DATE(hire_date), hire_date
FROM employee;

2. TIME( )

It extracts the time portion from a date/time value.

Example:
SELECT TIME(‘column_name’)
FROM table_name;

-- Extract the time and date from hire_date column.

SELECT TIME(hire_date), DATE(hire_date), hire_date
FROM employee;

3. EXTRACT( )

It extracts a specific component (year, month, day, etc.) from a date/time value.

Example:
SELECT EXTRACT(format FROM column_name)
FROM table_name;

-- Extract the year, month, day from hire_date column.

SELECT EXTRACT(YEAR FROM hire_date) as year, EXTRACT(MONTH FROM hire_date) as month, EXTRACT(DAY FROM hire_date) as day, hire_date
FROM employee;

4. YEAR(), MONTH(), DAY()

These functions are used to extract the year, month, or day from a date value.

Example:
SELECT YEAR(‘column’), MONTH(‘column’), DAY(‘column’)
FROM table_name;

-- Extract the year, month, day from hire_date column.

SELECT YEAR(hire_date) AS year, MONTH(hire_date) AS month, DAY(hire_date) AS day, hire_date
FROM employee;

5. NOW() , CURRENT_DATE, CURRENT_TIME

These functions are used to extract the current date and time.

Example:
SELECT NOW();

-- Display the current date and time.

SELECT NOW(), CURRENT_DATE, CURRENT_TIME, CURDATE(), CURTIME();

6. DATE_FORMAT()

It formats a date/time value into a specific format.

Example:
SELECT DATE_FORMAT(date, format)
FROM table_name;

%Y : Year as a numeric value, 4-digit value (2023)
%y : Year as a numeric value, 2-digit value (23)

%M : Month name in full (January to December)
%m : Month name as a numeric value (00 to 12)
%b : Abbreviated month name (Jan to Dec)

%d : Day of the month as a numeric value (01 to 31)
%D : Day of the month as a numeric value, followed by a suffix (1st)

%W: Weekday name in full (Sunday to Saturday)

%T : Time in 24-hour format (hh:mm:ss)
%H : Hour (00 to 23)
%i : Minutes (00 to 59)

-- Format: 01 Jan 2023

SELECT DATE_FORMAT(LEFT(hire_date, 10), "%d %b %Y") AS hiring_date_cleaned
FROM employee;

-- Format: 01 January 2023

SELECT DATE_FORMAT(LEFT(hire_date, 10), "%d %M %Y") AS hiring_date_cleaned
FROM employee;

-- Format: 01 Jan 2023 Monday

SELECT DATE_FORMAT(LEFT(hire_date, 10), "%d %b %y %W" ) AS hiring_date_cleaned
FROM employee;

7. TIMESTAMPDIFF( )

It calculates the difference between two date/time values in a specified unit.

Example:
SELECT TIMESTAMPDIFF(format, date1, date2)

-- Find out how many YEARS an employee has worked there.

SELECT emp_id, TIMESTAMPDIFF(YEAR, hire_date, CURRENT_DATE) AS employee_workingtime, hire_date
FROM employee
ORDER BY employee_workingtime DESC;

-- Find out how many MONTHS an employee has worked there.

SELECT emp_id, TIMESTAMPDIFF(MONTH, hire_date, CURRENT_DATE) AS employee_workingtime, hire_date
FROM employee
ORDER BY employee_workingtime DESC;

*** DATEDIFF(): calculates date differences on a per-day basis.

-- Find out how many DAYS an employee has worked there.

SELECT emp_id, DATEDIFF(CURRENT_DATE, hire_date) AS employee_workingtime, hire_date
FROM employee
ORDER BY employee_workingtime DESC;

8. DATE_ADD() and DATE_SUB()

They perform addition or subtraction operations on date values.

-- Subtract 2 months from hire_date column.

SELECT DATE_ADD(hire_date, INTERVAL -2 MONTH), hire_date
FROM employee;

-- Add 3 hours to hire_date column.

SELECT DATE_ADD(hire_date, INTERVAL 3 HOUR), hire_date
FROM employee;

-- Subtract 2 months from hire_date column.

SELECT DATE_SUB(hire_date, INTERVAL 2 MONTH), hire_date
FROM employee;

-- Subtract 3 hours from hire_date column.

SELECT DATE_SUB(hire_date, INTERVAL 3 HOUR), hire_date
FROM employee;

9. STR_TO_DATE()

It converts a string value to a date format.

SELECT STR_TO_DATE(column_name, format)
FROM table_name;

SELECT STR_TO_DATE('May 17, 2022', '%M %d, %Y');

-- Output: 2023–05–17

SELECT STR_TO_DATE(hire_date, '"%d %M %Y');

-- Output: 01 January 2023

10. WEEKDAY()

It returns the weekday (0–6, starting from Sunday) for a given date.

** Output: Sunday is 0 and Saturday is 6

Example:
SELECT WEEKDAY(column_name)
FROM table_name;

-- Find out what day employees started to work there.

SELECT WEEKDAY(hire_date), hire_date
FROM employee;

Conclusion

SQL Date & Time Functions are essential for working with date and time data in databases.

In this article, we have explored 10 key functions that can help you extract meaningful information, and perform calculations. By mastering these functions, you can perform complex date and time operations, create insightful reports, and gain deeper insights from your data.

source:google