Learning of SQL Day 13

 

Day 13: Date Functions

Introduction:

• Date functions in SQL are used to manipulate and retrieve date and time values. These functions are crucial for handling date-related data in your database.

Key Concepts:

• CURDATE(): Returns the current date.

• CURTIME(): Returns the current time.

• NOW(): Returns the current date and time.

• DATE(): Extracts the date part of a date or datetime expression.

• TIME(): Extracts the time part of a time or datetime expression.

• YEAR(), MONTH(), DAY(): Extracts the year, month, or day from a date.

• DATE_ADD(): Adds a specified time interval to a date.

• DATEDIFF(): Returns the difference between two dates.

SQL Commands and Examples:

1. CURDATE() Function:

   • Get the current date.

sql

-- Get the current date
SELECT CURDATE() AS CurrentDate;

2. CURTIME() Function:

   • Get the current time.

sql

-- Get the current time
SELECT CURTIME() AS CurrentTime;

3. NOW() Function:

   • Get the current date and time.

sql

-- Get the current date and time
SELECT NOW() AS CurrentDateTime;

4. DATE() Function:

   • Extract the date part of a date or datetime expression.

sql

-- Extract the date part from the current date and time
SELECT DATE(NOW()) AS CurrentDateOnly;

5. TIME() Function:

   • Extract the time part of a time or datetime expression.

sql

-- Extract the time part from the current date and time
SELECT TIME(NOW()) AS CurrentTimeOnly;

6. YEAR(), MONTH(), DAY() Functions:

   • Extract the year, month, or day from a date.

sql

-- Extract the year, month, and day from the current date
SELECT YEAR(CURDATE()) AS CurrentYear, MONTH(CURDATE()) AS CurrentMonth, DAY(CURDATE()) AS CurrentDay;

7. DATE_ADD() Function:

   • Add a specified time interval to a date.

sql

-- Add 7 days to the current date
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS DateAfterWeek;

8. DATEDIFF() Function:

   • Return the difference between two dates.

sql

-- Calculate the difference between two dates
SELECT DATEDIFF('2024-12-31', CURDATE()) AS DaysUntilYearEnd;

Practice Exercise:

1. Use the Students table (or any other table with date fields).

2. Write a query to get the current date.

3. Write a query to get the current time.

4. Write a query to get the current date and time.

5. Write a query to extract the year, month, and day from a date column.

6. Write a query to add 30 days to the current date.

7. Write a query to calculate the difference between two dates.

sql

-- Get the current date
SELECT CURDATE() AS CurrentDate;

-- Get the current time
SELECT CURTIME() AS CurrentTime;

-- Get the current date and time
SELECT NOW() AS CurrentDateTime;

-- Assume the Students table has a column DateOfBirth
-- Extract the year, month, and day from the DateOfBirth column
SELECT DateOfBirth, YEAR(DateOfBirth) AS BirthYear, MONTH(DateOfBirth) AS BirthMonth, DAY(DateOfBirth) AS BirthDay FROM Students;

-- Add 30 days to the current date
SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY) AS DateAfterMonth;

-- Calculate the difference between two dates
-- Example dates: 2024-12-31 and today's date
SELECT DATEDIFF('2024-12-31', CURDATE()) AS DaysUntilYearEnd;

Understanding and using date functions effectively allows you to handle date and time data in your database with precision.