The date functions in SQL play a vast role in managing and manipulating date and time data. In this article, we will explain all about the date functions in SQL, how to use them, best practices, and their benefits.
What are date functions in SQL?
Date functions are very essential for performing date and time data operations. Common date-time functions are as follows –
DAY(), MONTH(), YEAR(), ISDATE(), DATEFROMPARTS(), GETDATE(), SYSDATETIME(), GETUTCDATE(), Current_TimeStamp, DATEADD(), DATEDIFF(), DATENAME(), and DATEPART() etc.
Basics of date Functions in SQL
SQL offers plenty of date functions that care for various needs of businesses as follows –
- SQL date functions are a set of powerful tools that are designed to manipulate and manage date and time data within a database.
- Date functions in SQL can format dates according to the user’s or business-specific requirements.
- These functions allow the extraction of specific components from date and time data such as the user can retrieve individual elements like a year, month, day, or even the hour, minute, and second.
- The user can perform the analysis and filter data based on specific date ranges as per business requirements.
Common date functions in SQL
Refer below the common date functions in SQL-
DAY Function in SQL
The DAY Function is used to get the day of the month of a date.
Syntax
SELECT DAY(date);
Example
SELECT DAY('2020-07-02 06:30:45:000') AS DayPart;
MONTH Function in SQL
The MONTH Function is used to get the month of a year of a date.
Syntax
SELECT MONTH(date);
Example
SELECT MONTH('2020-07-02 06:30:45:000') AS MonthPart;
YEAR Function in SQL
The YEAR Function is used to get the year part of a date.
Syntax
SELECT YEAR(date);
Example
SELECT YEAR('2020-07-02 06:30:45:000') AS YearPart;
ISDATE Function in SQL
The ISDATE Function is used to check the given expression for date and provide a result as 1 if the expression is a valid date else return zero(0).
Syntax
SELECT ISDATE(expression);
Example
SELECT ISDATE('2020-07-02 06:30:45:000') AS CheckDate;
SELECT ISDATE('SQLPOST Academy') AS CheckDate;
In the above example, we have written 2 different queries and provided two different expressions like in the first date and the second character string. The function has returned 1 for date expression and 0 for string expression.
DATEFROMPARTS Function in SQL
The DATEFROMPARTS Function is used to get specific date parts values like year, month, and day and return a date using those values. It has 3 mandatory parameters year, month, and day where values should be 4 digits for the year part, from 1 to 12 values for the month part, and from 1 to 31 values for the day part.
Note: This function was introduced in the 2012 edition of MS SQL server; hence, please do not try in its earlier versions.
Syntax
SELECT DATEFROMPARTS(year, month, day);
Example
SELECT DATEFROMPARTS(2020, 7, 2) AS DATEFROMPARTS_Date;
GETDATE Function in SQL
The GETDATE Function is used to get the database’s current system date and time in the ‘YYYY-MM-DD hh:mm:ss.mmm’ format.
Syntax
SELECT GETDATE();
Example
SELECT GETDATE() AS GetCurrentDate;
SYSDATETIME Function in SQL
The SYSDATETIME Function is used to get the SQL server’s current date and time in the ‘YYYY-MM-DD hh:mm:ss.mmm’ format.
Syntax
SELECT SYSDATETIME();
Example
SELECT SYSDATETIME() AS GetSysDateTime;
GETUTCDATE Function in SQL
The GETUTCDATE Function is used to get the database’s current system UTC date and time in the ‘YYYY-MM-DD hh:mm:ss.mmm’ format. The abbreviation of UTC is Universal Time Coordinate or Greenwich Mean Time.
Syntax
SELECT GETUTCDATE();
Example
SELECT GETUTCDATE() AS GetCurrentUTCDate;
Current_TimeStamp Function in SQL
The Current_TimeStamp Function is used to get the current date and time into the ‘YYYY-MM-DD hh:mm:ss.mmm’ format.
Syntax
SELECT CURRENT_TIMESTAMP;
Example
SELECT CURRENT_TIMESTAMP;
DATEADD Function in SQL
The DATEADD Function is used to add intervals of date and time of a date field and return a desired date. It has 3 mandatory parameters interval, number, and date.
Syntax
SELECT DATEADD(interval, number, date);
Example
SELECT '2020-07-02' AS OldDate, DATEADD(MONTH, 2, '2020-07-02') AS NewDate;
In the above example, we have added 2 months to the given old date, and similarly, we can add a value in year, month, day, hours, minute, second, and mili second with positive and negative both as per requirements. Please refer to some examples for more clarity –
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AS 'First Day of Current Week'
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) AS 'Last Day of Current Week'
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS 'First Day of Last Week'
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) AS 'Last Day of Last Week'
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS 'First Day of Current Month'
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) AS 'Last Day of Current Month'
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS 'First Day of Last Month'
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) AS 'Last Day of Last Month'
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) AS 'First Day of Current Year'
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) AS 'Last Day of Current Year'
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AS 'First Day of Last Year'
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) AS 'LastDay of Last Year'
DATEDIFF Function in SQL
The DATEDIFF Function is used to get the difference between two different date field values and return a desired date. It has 3 mandatory parameter intervals, date1, and date2.
Syntax
SELECT DATEDIFF(interval, date1, date2);
Example
SELECT '1947-08-15' AS DOB, DATEDIFF(YEAR, '1947-08-15','2020-07-02') AS Age;
In the above example, we have tried to get an interval (difference of age) in the year between two different dates. Please refer to some examples for more clarity –
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(YEAR, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS YearInterval;
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(QUARTER, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS QuarterInterval;
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(MONTH, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS MonthInterval;
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(DAYOFYEAR, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS DayOfYearInterval;
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(DAY, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS DayInterval;
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(WEEK, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS WeekInterval;
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(WEEKDAY, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS WeekDayInterval;
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(HOUR, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS HourInterval;
SELECT '1947-08-15 09:30:25:100' AS OldDate, DATEDIFF(MINUTE, '1947-08-15 09:30:25:100','2020-07-02 06:30:45:000') AS MinuteInterval;
DATENAME Function in SQL
The DATENAME Function is used to get a date and returns a specific part of the date. It has 2 mandatory parameters interval and date. This function provides the result as a character string value.
Syntax
SELECT DATENAME(interval, date);
Example
SELECT DATENAME(YEAR, '2020-07-02') AS YearPart;
In the above example, we have provided a date and it returned a year part of the date as we have given the YEAR interval parameter. Please refer to some examples for more clarity –
SELECT DATENAME(YEAR, '2020-07-02 06:30:45:000') AS YearPart;
SELECT DATENAME(QUARTER, '2020-07-02 06:30:45:000') AS QuarterPart;
SELECT DATENAME(MONTH, '2020-07-02 06:30:45:000') AS MonthPart;
SELECT DATENAME(DAYOFYEAR, '2020-07-02 06:30:45:000') AS DayOfYearPart;
SELECT DATENAME(DAY, '2020-07-02 06:30:45:000') AS DayPart;
SELECT DATENAME(WEEK, '2020-07-02 06:30:45:000') AS WeekPart;
SELECT DATENAME(WEEKDAY, '2020-07-02 06:30:45:000') AS WeekDayPart;
SELECT DATENAME(HOUR, '2020-07-02 06:30:45:000') AS HourPart;
SELECT DATENAME(MINUTE, '2020-07-02 06:30:45:000') AS MinutePart;
SELECT DATENAME(SECOND, '2020-07-02 06:30:45:000') AS SecondPart;
SELECT DATENAME(MILLISECOND, '2020-07-02 06:30:45:000') AS MilliSecondPart;
DATEPART Function in SQL
The DATEPART Function is used to get a date and returns a specific part of the date. It has 2 mandatory parameters interval and date similar to DATENAME only the difference is that this function provides the result as an integer value.
Syntax
SELECT DATEPART(interval, date);
Example
SELECT DATEPART(YEAR, '2020-07-02') AS YearPart;
In the above example, we have provided a date and it returned a year part of the date as we have given the YEAR interval parameter. Please refer to some examples for more clarity –
SELECT DATEPART(YEAR, '2020-07-02 06:30:45:000') AS YearPart;
SELECT DATEPART(QUARTER, '2020-07-02 06:30:45:000') AS QuarterPart;
SELECT DATEPART(MONTH, '2020-07-02 06:30:45:000') AS MonthPart;
SELECT DATEPART(DAYOFYEAR, '2020-07-02 06:30:45:000') AS DayOfYearPart;
SELECT DATEPART(DAY, '2020-07-02 06:30:45:000') AS DayPart;
SELECT DATEPART(WEEK, '2020-07-02 06:30:45:000') AS WeekPart;
SELECT DATEPART(WEEKDAY, '2020-07-02 06:30:45:000') AS WeekDayPart;
SELECT DATEPART(HOUR, '2020-07-02 06:30:45:000') AS HourPart;
SELECT DATEPART(MINUTE, '2020-07-02 06:30:45:000') AS MinutePart;
SELECT DATEPART(SECOND, '2020-07-02 06:30:45:000') AS SecondPart;
SELECT DATEPART(MILLISECOND, '2020-07-02 06:30:45:000') AS MilliSecondPart;
Disadvantages of Using date Functions in SQL
While date functions in SQL are very powerful they can impact query performance. It is advisable to avoid extensive date manipulation in WHERE clauses that help optimal database performance. Utilize indexes, optimize queries, and consider alternatives to ensure efficient database operations. When working with dates, accounting for null values is crucial. Strategies like using the COALESCE function can prevent unexpected errors.
Best Practices for Date Functions in SQL
To use the full potential of date functions it is advised to adopt best practices as follows –
- Implement the indexes on columns containing date data which enhances query performance.
- Optimization strategy like Proper implementation of indexes helps for faster data retrieval.
- Regularly maintain of database, schedule run-time checks, and update activities to prevent data inconsistencies and ensure smooth functioning of date functions.
- Selection of the appropriate date function for the task at hand and handling NULL values with care.
- Being mindful of the syntax variations across different database systems.
FAQs
Use the BETWEEN clause along with the appropriate date functions like GETDATE() for filtering data within a specified range.
Utilize functions like COALESCE to handle null values gracefully.
Yes, Date Functions can handle NULL values, but it’s essential to account for them in your queries to avoid unexpected results.
Functions like CAST() and CONVERT() helps you to format dates according to your preferences, ensuring clear and readable outputs.
To extract the year from a date, use the YEAR() function. For example, SELECT YEAR(‘2023-11-18’) returns ‘2023’.
Yes, some databases provide alternative functions or methods for date manipulation. Check the documentation for your specific database.
Yes, most relational databases support standard date functions. However, syntax might vary slightly between the different databases.
While some functions can impact performance, optimizing queries and using indexes can mitigate resource issues.
Absolutely! SQL provides functions like DATEADD() for adding time intervals and DATEDIFF() for finding the difference between two dates.
Conclusion
Mastering date functions in SQL is essential for effective database management. Whether you are a beginner or an experienced SQL server, understanding these functions will enhance your ability to work with dates and times seamlessly.
We hope you like this article very well. Please share it on your social media network, which may be beneficial for others. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response ASAP.
