Table of Contents
SQL Date Functions - Overview
In this article, you are going to learn about SQL Date Functions. Each SQL Date Function will be explained in the context of different Database Management Systems like MySQL and MS SQL Server. Some SQL Date Functions are similar in most database management systems. Few SQL Date Functions like CURDATE() and GETDATE() does the same functionality but CUTDATE SQL Date Functions belongs to MySQL while GETDATE() belongs to MS SQL Server.
Before we start, let's recap about the Date and time data types that are used to store DateTime in SQL. SQL DateTime data type is the primitive data type that can also store timestamp and year. Different Database Management Systems may or may not use all primitive data types of SQL. SQL DATE data type is not used by ORACLE. It is being used in most database management systems like SQL Server, MySQL, MS Access, etc.
For example, the Oracle database does not support DateTime and my SQL does not support CLOB data type so while designing database schema and writing SQL queries make sure to check If the data types are supported are not. SQL date is the most popular data type being used in most of the common relational database vendors.
SQL Date and Time Data Types
Here is the list of SQL date and time data types that are widely used by most database management systems:
- DATE data type can store only the date in the format YYYY-MM-DD
- TIME data type can only store the time in the format HH:MI:SS
- DATETIME data type can store both the date and the time information in the format YYYY-MM-DD HH:MI:SS
- TIMESTAMP data type can store the date and the time between a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
- YEAR(MySQL) data type can store only the year in 2 digits or 4 digit format. It can store 4 digit format the year between 1901 to 2155 and in 2-digit format from 70 to 69, representing 1970 to 2069.
- SmallDateTime(SQL Server) data type can store only the date in YYYY-MM-DD hh:mm:ss format.
- DateTime2 data type can store the date and the time in YYYY-MM-DD hh:mm:ss[.nnnnnnn] format. It can store nanoseconds.
Working With SQL Date Functions
Working with SQL Dates is the difficult part for the SQL programmer and the practitioner. it becomes more critical and can create logical errors when one is not sure about the format of the Date being inserted. Output results can contain nothing while finding the matches for dates without knowing about the format of the date column in the database.
As long as your data contains only the date portion, it is easy to handle with the help of various SQL Date Functions. But it becomes more complicated if the time portion is also stored in the database with the date.
In this article, you will learn to work with SQL Date functions with the help of real-life practical examples. In these examples, different scenarios will be demonstrated in which one needs while writing SELECT queries including the date and the time. For this, you may need to use the built-in SQL Date function available in almost every DBMS like SQL Server, MySQL, MS Access, ORACLE, etc.
Different Types of SQL Date Functions
Here is the list of the most commonly used SQL Date functions that are used to manipulate the date and the time stored in the database as per the requirement of the SELECT Query. We will try to explain one by one SQL Date Function with relevant examples and the SELECT statement example code.
1 ADDDATE() SQL DATE Function
This function is used to add an interval of days in the Date. Suppose a university instructor wants to extend the date of the final submission date for an assignment. Let's look at the SQL Server DATEADD function Code example, Instructor wants to add 5 more days in the DueDate Column in the following Table (AssSubmission).
UPDATE AssSubmission set DueDate= DATEADD(DAY, 5,DueDate) From AssSubmission;
2. CURDATE() / GETDATE() SQL Date Function
CURDATE() SQL Date function is used to return the current date of the system in MySQL DBMS. GETDATE() SQL date function is used to return the current date in MS SQL Server. Both these functions have the same functionality but are from different DBMS.
Suppose you want to see the orders received on the current day. Let see the example of the CURDDATE() SQL date function Code in which the SELECT command is used to see the list of all orders from the ORDER Table which is received on the current date(Today). The figure below is the snapshot of the Orders Table taken on 08/08/2021. One thing must be sure that the OrderDate field must have a DATE data type.
After executing the following Select query with SQL Date Function CURDATE() the resultant output will be:
SELECT Orders.CustomerID, Orders.orderstatus, Orders.CartID, Orders.OrderDate FROM Orders WHERE (((Orders.OrderDate)=CURDATE());
SELECT Orders.CustomerID, Orders.orderstatus, Orders.CartID, Orders.OrderDate FROM Orders WHERE (((Orders.OrderDate)=GETDATE());
3. DATE_FORMAT() - MySQL Date Functions
This SQL Date function is used to retrieve the Date as per the specified format. In most countries, different date Formats are being used like DD/MM/YY, DD/MM/YYYY, MM/DD/YY, etc. In the above "Output Table 2 - CURDATE/GETDATE SQL Date Function", you can see the date is shown in MM/DD/YYYY Format. When The date is retrieved with CURDATE() /GETDATE() SQL Date Functions the format is the same again. If you want to see the Date value in some specific format, you can use the DATE_FORMAT() in MySQL and FORMAT() in MS SQL Server and MS Access.
Suppose you want to see the OrderDate in DD/MM//YYYY. Here is a summary of DATE Formats that can be specified in MySQL and SQL Server. In MySQL, the DATE_FORMAT Function is used to format the date in MySQL while in SQL Server only the FORMAT function is used to format the SQL Dates in specified Format.
Before starting the MySQL Practical Example, We first create a Student Table separately to perform SELECT queries and to perform DATE_FORMAT() SQL Date Function. We will later use this table for further examples in the preceded text whenever we will work in MySQL Date functions.
CREATE TABLE IF NOT EXISTS `student` ( `id` int(6) unsigned NOT NULL, `stName` varchar(30) DEFAULT NULL, `BirthDate` date DEFAULT NULL, `city` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Insertring data for table `student` -- INSERT INTO `student` (`id`, `stName`, `BirthDate`, `city`) VALUES (1, 'Steward', '2021-03-02', 'London'), (2, 'Lucman', '2000-08-19', 'New York'), (3, 'Brathwaite', '2001-01-07', 'Oslo'), (4, 'Ahmad Khan Kazmi', '1999-11-05', 'Mumbai'), (5, 'Joe Stephen', '2003-08-25', 'China');
The syntax for DATE_FORMAT SQL DATE Functions :
SELECT student.id, student.stname, student.city, DATE_FORMAT( student.birthdate, "%d/%m/%Y" ) AS FormattedDate FROM student;
Date_Format() function has changed the output format of the column "BirthDate" in this output Table 3. Now the date is being shown in "dd/mm/yyyy" format as it is specified in the SELECT Statement.
4. The FORMAT() SQL Date Function of MS SQL SERVER
Let's apply now the FORMAT() SQL Date Function on above "Example Table 2" consisting of the order information. A similar output will be generated with the use of the FORMAT() function of MS SQL Server. It is to be noted here that Format() functions can only be used in MS SQL Server 2012 or higher. The older versions do not support the FORMAT() SQL Date function.
Here is the Syntax and SELECT Query with the use of the FORMAT() SQL Date Function to see the orderdate in "dd-mm-yyyy" format.
The Syntax for the FORMAT() Function
Format (value, format, culture)
(culture argument is optional in SQL Server 2017).
SELECT Orders.CustomerID, Orders.orderstatus, Orders.CartID, Format(Orders.OrderDate,"dd-mm-yyyy") AS FormattedDate FROM Orders;
5. DAY(), MONTH() and YEAR() SQL DATE Functions
These SQL Date functions are common in use. The SQL Day() function returns the day number of the given date. Month() function returns the Month number of the given date. Similarly, the Year() Function returns the year number of the given date. Suppose you want to break the date into day, month, and year. These functions will give you more control. If you want to extract the day number, month number, and year number from a date, these SQL Date functions can do it for you.
Here is an example of the Orders Table shown above in "Example Table2". In this query, we will break the orderdate value into the day, month, and Year.
SELECT Orders.CustomerID, Orders.OrderDate, Day([OrderDate]) AS OrderDay, Month([OrderDate]) AS OrderMonth, Year([OrderDate]) AS OrderYear FROM Orders;
It can be observed that for CustomerID 1, OrderDate is 13 December 2021. Day() functions returned the valued 13, Month() function return the value 12 and Year() function returned the valued 2021. Similarly in the other rows of the above table, you can observe.
6. DAYNAME(), MONTHNAME() SQL Date Functions
Suppose you have a date value and you want to know what day of the week it occurred. For instance, you have the date – 2021-01-12 and you want to know the weekday name of that date. Similarly in the case of the month name. DAYNAME() is in the list of SQL Date Function which will return the name of the day. MONTHNAME() SQL Date Function will return the name of the month.
Suppose you want to find the number of students who have joined the new batch on Monday, Tuesday and so on. For this, we will make use of the DAYNAME() function with COUNT and Group BY Clause as under. The snapshot of the Students Table is shown here
SELECT DAYNAME(Joining_date) AS Day, Count(*) AS NumberOfStudents FROM Students GROUP BY Day;
In Students Table, a total of 5 students have joined the new batch. On 08/02/2021 (mm-dd-yyyy), it was Monday, and on this date, you can see a total of 2 students have been joined. Similarly, on 08/03/2021, it was Tuesday as per the calendar the DAYNAME() functions returned the day name.
7. DATEPART() SQL Date Function of MS SQL SERVER
DATEPART() SQL Date function is widely used in real-life scenarios. For example, if you want to calculate the no of working days of an employee, you want to calculate the age of any student or employee in days, months, and years. You will have to Extracts a part of the date from the date and then need other DATE_SUB or DATE_DIFF Functions to complete this.
Here interval means what part you want to extract from the date. It can be multiple values as mentioned in the MSDN documentation to the SQL Server. Here are few examples of the interval values that it can take.
- For extracting Year as interval "year, yyyy, yy" can be mentioned as the first parameter
- For extracting Month as interval "month, mm,m" can be mentioned as the first parameter
- For extracting daysofyear as interval "dayofyear, dy, y" can be mentioned as the first parameter
- Similarly, "day, dd, d" for Day of the month, "week, ww, wk" for Week, and "weekday, dw, w" for Weekday
Let you want to evaluate the age of the student on the current date. The age of the students can be calculated during admissions of the students in years. DatePart() function and GETDATE() function can used to calculate age. The snapshot of the Students Table is shown here.
Here is the SELECT query to perform the most common example about "How to calculate age (in years) based on Date of Birth using DATEPART and GETDATE() Functions of SQL Server | Calculating the Age of the Students from Current Date |
SELECT ID, stName, BirthDate, (DATEPART(year, GETDATE())- DATEPART(year, BirthDate)) AS StudentAge from student
Observer the output, age of the Students are calculated in years in this out Table in Column no. 4 as StudentAge. In the SELECT statement, you can observe that the DATEPART Function of SQL Server is taking two parameters "year" the Date. It extracts the year from the given date based on the first parameter as mentioned "year" from the second parameter which must be a Date type value or Field.
These functions are used to get the part of the given date. DATEPART() is the function of MS SQL Server while EXTRACT is the function of MySQL.
8. MySQL EXTRACT() DATE Function
Let's do the above example in MySQL. The EXTRACT() function of MySQL is used to extract a part from a given date. MySql provides more general and easy syntax as compared to the DATEPART() function of SQL Server.
EXTRACT(part FROM date)
Two things we must pass in the function: part and Date. Part is like the interval parameter in SQL Server and Date is the same as in DATEPART() function. But you can see the syntax is more simple and generic like SELECT EXTRACT(MONTH FROM "2017-06-15");
Here in this example, the MONTH means we want to extract the Month from the given Date "2017-06-15". The date can the mentioned manually, with function or any column name of the table.
The "part" can have the following values:
- It can have many values related to a time calculation that you can use when needed to calculate the time intervals in real-life situations.
Let's again do the most common example about "How to calculate age (in years) based on Date of Birth using DATEPART() SQL Date Function in MySQL.
For this example, let us use the above Student Table shown in "Example Table 3 - The Student". SELECT Query for calculating the age in years for students in MySQL will be as under :
SELECT ID, stName, BirthDate, (EXTRACT(year from CURDATE())- EXTRACT(year FROM BirthDate)) AS StudentAge from student
There are two differences between this MySQL Select query and the Select Query of MS SQL Server.
- The first difference is the GETDATE() SQL Date Function which is used to get the current system date. In MySQL Server, you can see the CURATE() SQL Date Function is being used in the above SELECT Query to calculate the age in years example using MySQL.
- The Second is the use of EXTRACT() SQL Date Function and the parameter used in this. "year form CURDATE()", and "year from BirthDate()" are easier and readable to understand for the learners.
The results in the output Table are the same as it has been in the above "Output Table 7 ".
The SQL Date Functions are widely used by database developers in the manipulation of dates. This is a difficult part of the database design. Data and time are handled differently by DBMS as they are displayed in tables. When you need to perform some arithmetic and logical operations on stored dates and time values in the database, different SQL Data and Time Functions are available there to help you. Different functions perform different mathematical and logical functions based on their syntax and definitions. Some of these functions are so common to use like DATEPART(), DAY(), MONTH(), YEAR(), FORMAT(), DATE_FORMAT(), DATEADD(), DATEDIFF() etc.