SQL Tutorial Series - Video 27: Date and Time Functions

Overview

• This video focuses on Date and Time Functions in SQL.
• Previous videos covered: Aggregate Functions, String Functions, Numeric Functions.
• Practice questions provided at the end of the video.

Built-in Functions in SQL

1. Aggregate Functions
2. String Functions
3. Numeric Functions
4. Date and Time Functions
5. Conversion Functions (upcoming in next video)

Key Date and Time Functions

1. Current Date, Time, and Timestamp

• Current Date: Returns the current date.
  • Usage: SELECT CURRENT_DATE;
• Current Time: Returns the current time.
  • Usage: SELECT CURRENT_TIME;
• Current Timestamp: Returns the current timestamp (date + time).
  • Usage: SELECT CURRENT_TIMESTAMP;

2. Date and Time Extraction

• Date Function: Extracts only the date from a timestamp.
  • Usage: SELECT DATE(current_timestamp);
• Time Function: Extracts only the time from a timestamp.
  • Usage: SELECT TIME(current_timestamp);
• Year Function: Extracts the year from a date or timestamp.
  • Usage: SELECT YEAR(date_time_expression);
• Month Function: Extracts the month from a date or timestamp.
  • Usage: SELECT MONTH(date_time_expression);
• Day Function: Extracts the day from a date or timestamp.
  • Usage: SELECT DAY(date_time_expression);

3. Extract Function

• Extract Function: Returns a specific component (year, month, day, hour, etc.) from a date or timestamp.
  • Syntax: EXTRACT(component FROM date_time_expression);
  • Example: SELECT ID, NAME, EXTRACT(DAY FROM date_of_birth) FROM employee;

4. Date Difference

• Date Difference Function: Returns the difference between two dates.
  • Syntax: DATEDIFF(date1, date2);
  • Note: date1 must be later than date2.
• Time Difference Function: Returns the difference between two times.
  • Syntax: TIMEDIFF(time1, time2);

5. Date Add and Date Subtract

• Date Add Function: Adds a date or time interval to a date/timestamp.
  • Syntax: DATE_ADD(date_time_expression, INTERVAL value unit);
• Date Subtract Function: Subtracts a date or time interval from a date/timestamp.
  • Syntax: DATE_SUB(date_time_expression, INTERVAL value unit);

6. Date Format Function

• Date Format Function: Displays a date/timestamp in a specified format.
  • Syntax: DATE_FORMAT(date_time_expression, 'format_string');
  • Example Format String: %d %b %Y (Day, Month name, Year).

Example Queries

1. Adding One Month:

   SELECT ID, NAME, DATE_ADD(date_of_birth, INTERVAL 1 MONTH) AS updated_date_of_birth FROM employee;  
   

2. Subtracting Three Years:

   SELECT ID, NAME, DATE_SUB(date_of_birth, INTERVAL 3 YEAR) AS updated_date_of_birth FROM employee;  
   

3. Formatting Date:

   SELECT ID, NAME, DATE_FORMAT(date_of_birth, '%d %b %Y') AS new_date_of_birth FROM employee;  
   

Conclusion

• Some functions may not work in all DBMS.
• Viewers are encouraged to ask questions in the comments for DBMS-specific issues.
• SQL practice PDF link available in the description for further practice.
• Next video will cover conversion functions.