EDUCBA

Home Data Science Data Science Tutorials SQL Tutorial SQL Date Functions

SQL Date Functions

Introduction to SQL Date Functions

The following article provides an outline for SQL Date Functions. The date is the most vital function used in SQL; at times, it is hard for beginners to work with it as they are many formats and types when it comes to date and time functions. The retrieval of those function is varied among the user according to their requirement. In the SQL database, the time format is applied along with the date function, and at times both time and date values are stored in a single column.

SQL Date Functions

Key Takeaways

  • CURRENT_TIMESTAMP gives the present date and time.
  • DATE_ADD is used to add an interval to the date.
  • DATEDIFF() is used to find the difference between two dates.
  • EXTRACT YEAR FROM DATE retrieves the year from the date. And same goes with month from date and day from date.
  • Convert date to string and string to date format can be used when required.

What are SQL Date Functions?

The DATE function in SQL executes the progressive serial numbers which define the particular date. The syntax of the date function is DATE(year, month, day). The year value can be given in four or two digits. And importantly, the year and month argument should be passed.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

In SQL, the date functions can be called an array of functions that work on a particular date and enable the users to fetch the present date or time in the respective time zone or retrieve only the year or month, or date or, at times, difficult options like fetching the end date of month or time zone can also be possible with SQL date functions. SQL enables the user to imply this required format of date and time functions with simple and standard queries.

SQL Date Function Database

The data type of DATE saves the calendar date, and it consumes four bytes. The calendar date is saved as an internal one which is an integer value and is equal to the count of days from December 31, 1899. Here the date value is saved as an integer, and the user can work with them as arithmetic expressions. In an SQL server, every local variable, column, row, expression, and unit parameter has the associated date functions.

Here are a few examples:

The below table is named EDUCBA.

Id course name starting date starting time.

001 AWS 01-01-2023 09.00.00

Now the queries can be executed as:

Command:

SELECT course name, DATE_ADD, starting date INTERVAL 2 year FROM EDUCBA

Now the starting date has been modified from EDUCBA.

Course name    Starting date

AWS       01-01-2025 09.00.00

The same format can be applied to change time and date with before, after, and interval formats.

DATE_SUB() is used to subtract the given time interval from the date.

DATADIFF() is used to execute the number of days between two dates.

Types of SQL Date Function

The date functions of SQL are given below:

1. ADDDATE() is used to execute the information after a specific date or time interval that has been included.

Syntax:

SELECT ADDDATE ("2022-07-16 04:52:47", "4");

Output:

2022-07-16 04:52:47

2. ADDTIME() is used to execute a time or date after a specific interval has been included.

Syntax:

SELECT ADDTIME("2022-07-16 04:52:47", "4");

Output:

is 2022-07-16 04:52:47

3. CURDATE() is used to execute the present date.

Syntax:

SELECT CURDATE();

Output:

2022-11-28

4. CURRENT_TIME() is used to execute the present time.

Syntax:

SELECT CURRENT_TIME();

Output:

02:53:15

It can also be applied to the second, minute, microseconds, hour, weeks, day, months, year, quarters, etc

SQL Date Server

The SQL server can be operated with the below format to save the time value or date value in the database. Any data type can be used to create a new table or new column in the database.

The format of DATE is YYYY-MM-DD

The format of DATETIME is YYYY-MM-DD – HH:MI:SS

The format of SMALLDATETIME is YYYY-MM-DD – HH:MI:SS

The format of TIMESTAMP comes as a unique numeric value.

The DATE functions work as an argument of any value, and then it changes the value to a particular DATETIME value. It computes the string with respect to the directives of date formatting and executes the exact value of date and time.

SQL Date Function List Table

DATE_FORMAT() is used to display the time or date in different formats.

Syntax:

TIME_FORMAT(time,format);

The date should be a valid one, and the format can be used from the below options:

  • %a is the Abbreviated name of the weekday (Sun-Sat)
  • %b is the Abbreviated name of the month(Jan-Dec)
  • %c explains Month and numeric (0-12)
  • %D denotes the Day of any month with English suffix (0th, 1st, 2nd, 3rd)
  • %f gives microseconds (000000-999999)
  • %H gives the Hour (00-23)
  • %I gives Hour in 12-hour format (01-12)
  • %i gives the Minutes in number format (00-59)
  • %j gives the Day of year (001-366)
  • %k gives the hour (0-23)
  • %M gives the Month name from (January-December)
  • %m gives the number of the month from 0 to 12
  • %p says AM or PM
  • %s shows the Seconds (00-59)
  • %T shows the Time in 24-hour (hh:mm: ss)
  • %Y shows the Year in numbers with four digits
  • %y shows the Year in numbers with two digits

FAQs

Given below are the FAQs mentioned:

Q1. How to use date functions?

Answer: The purpose of date functions is to manipulate the info of time and date effectively. The user can simply vary functions like adding days to date, computing the difference between dates, and so on.

Q2. How to use the year function?

Answer: In SQL, the YEAR function accepts the valid date or DateTime format and executes the part of the year as the value of an integer.

Q3. How to use CONVERT_TZ functions?

Answer: It is used to change the given date and time from one time zone to another time zone. If the function is invalid, it executes null.

Conclusion

Hence in this topic, the brief functions of SQL date and time functions are explained. Even beginners can work effectively with standard queries in SQL on date and time functions. The availability of different formats in date and time functions helps in logical and statistical analysis when dealing with higher dimensional data.

Recommended Articles

This is a guide to SQL Date Functions. Here we discuss the introduction, SQL date function database, types, server, and list table. You can also go through our other suggested articles to learn more –

  1. Oracle PL/SQL Interview Questions
  2. What is SQL Injection?
  3. What is T-SQL?
  4. Different View Options in MySQL