The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Date and Time Functions

Next ►
►

In this section, we will explore various SQL functions that allow you to manipulate and work with date and time data. These functions are essential for performing operations such as extracting specific parts of a date, calculating differences between dates, and formatting date and time values.

Key Concepts

  1. Date and Time Data Types: Understanding the different data types used to store date and time values.
  2. Extracting Date and Time Parts: Functions to extract specific parts of a date or time.
  3. Date Arithmetic: Performing calculations with dates and times.
  4. Formatting Date and Time: Converting date and time values to different formats.

Date and Time Data Types

SQL provides several data types to store date and time values. The most common ones are:

  • DATE: Stores date values (year, month, day).
  • TIME: Stores time values (hour, minute, second).
  • DATETIME: Stores both date and time values.
  • TIMESTAMP: Similar to DATETIME, but also includes time zone information.
  • YEAR: Stores year values.

Extracting Date and Time Parts

Example: Extracting Year, Month, and Day

SELECT 
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

Explanation

  • EXTRACT(YEAR FROM order_date): Extracts the year part from the order_date column.
  • EXTRACT(MONTH FROM order_date): Extracts the month part from the order_date column.
  • EXTRACT(DAY FROM order_date): Extracts the day part from the order_date column.

Other Useful Functions

  • YEAR(date): Returns the year part of the date.
  • MONTH(date): Returns the month part of the date.
  • DAY(date): Returns the day part of the date.
  • HOUR(time): Returns the hour part of the time.
  • MINUTE(time): Returns the minute part of the time.
  • SECOND(time): Returns the second part of the time.

Date Arithmetic

Example: Adding and Subtracting Dates

SELECT 
    order_date,
    order_date + INTERVAL '1 year' AS next_year,
    order_date - INTERVAL '1 month' AS previous_month
FROM orders;

Explanation

  • order_date + INTERVAL '1 year': Adds one year to the order_date.
  • order_date - INTERVAL '1 month': Subtracts one month from the order_date.

Other Useful Functions

  • DATE_ADD(date, INTERVAL value unit): Adds a specified interval to a date.
  • DATE_SUB(date, INTERVAL value unit): Subtracts a specified interval from a date.
  • DATEDIFF(date1, date2): Returns the difference in days between two dates.

Formatting Date and Time

Example: Formatting Dates

SELECT 
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date,
    DATE_FORMAT(order_date, '%W, %M %d, %Y') AS long_format
FROM orders;

Explanation

  • DATE_FORMAT(order_date, '%Y-%m-%d'): Formats the order_date as YYYY-MM-DD.
  • DATE_FORMAT(order_date, '%W, %M %d, %Y'): Formats the order_date in a long format, e.g., Monday, January 01, 2023.

Common Date Format Specifiers

Specifier Description Example Output
%Y Year (4 digits) 2023
%y Year (2 digits) 23
%M Full month name January
%m Month (2 digits) 01
%D Day with suffix 1st
%d Day (2 digits) 01
%H Hour (24-hour) 14
%h Hour (12-hour) 02
%i Minutes 30
%s Seconds 45
%p AM or PM PM

Practical Exercises

Exercise 1: Extracting Date Parts

Task: Write a query to extract the year, month, and day from the order_date column in the orders table.

SELECT 
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

Exercise 2: Date Arithmetic

Task: Write a query to add 2 weeks to the order_date and subtract 3 days from the order_date in the orders table.

SELECT 
    order_date,
    order_date + INTERVAL '2 week' AS two_weeks_later,
    order_date - INTERVAL '3 day' AS three_days_earlier
FROM orders;

Exercise 3: Formatting Dates

Task: Write a query to format the order_date as YYYY-MM-DD and as Day, Month DD, YYYY in the orders table.

SELECT 
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date,
    DATE_FORMAT(order_date, '%W, %M %d, %Y') AS long_format
FROM orders;

Common Mistakes and Tips

  • Incorrect Data Types: Ensure that the columns you are working with are of the correct date or time data type.
  • Time Zones: Be aware of time zone differences when working with TIMESTAMP data types.
  • Date Formats: Use the correct format specifiers when formatting dates to avoid errors.

Conclusion

In this section, we covered the essential date and time functions in SQL. You learned how to extract specific parts of a date, perform date arithmetic, and format date and time values. These skills are crucial for handling date and time data effectively in your SQL queries. In the next section, we will delve into conditional expressions, which will further enhance your ability to write complex and dynamic SQL queries.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project

The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved