What are The Date Functions in SQL | Practical Examples

In this article, we will discuss Date Functions in SQL with real-time scenarios. Here I will show you the practical scenarios with syntax so that you can write your own SQL Queries depending on your requirements.

At the end of this article, I also captured the video tutorial for your better understand. So make sure to watch that video as well.

Before we jump on to the Practical Example let us see the use and syntax of these SQL Date Functions.

Date Functions in SQL With Syntax

Below are the commonly used Date Functions in SQL. These functions are very important and which are frequently being used in the SQL data retrievals

• Year
• Month
• Date Name
• Day
• IS DATE

Year – Returns the ‘Year number’ of the given date

Examples:
Select Year(GETDATE()) -- Returns the year number, based on the current system date
Select Year('02/31/2014') -- Returns 2014

Month – Returns the ‘Month number of the year’ of the given date

Examples:
Select Month(GETDATE()) -- Returns the Month number of the year, based on the current system date and time
Select Month('05/31/2018') -- Returns 5

Date Name(DatePart, Date) – Returns a string, that represents a part of the given date. This function takes 2 parameters.

The first parameter ‘DatePart’ specifies, the part of the date, we want. The second parameter is the actual date , from which we want the part of the Date.

Day – Returns the ‘Day number of the Month’ of the given date

Examples:
Select DAY(GETDATE()) -- Returns the day number of the month, based on current system DateTime.
Select DAY('06/28/2012') -- Returns 28

IS DATE – Checks if the given value is a valid date, time, or DateTime. Returns 1 for success, 0 for failure.

• Examples:
• Text usage Select ISDATE(‘SQLQUERIES’) — returns 0
• System Date use Select ISDATE(Getdate()) — returns 1
• Static Date Select ISDATE(‘2013-11-25 20:03:04.169’) — returns 1

Note: For date time 2 values, Is Date returns ZERO.

Example:
Select ISDATE('2019-10-01 11:34:22.1918455') -- returns 0.

Date Name (Date Part, Date) – Returns a string value, that represents a part of the given date.

This function takes 2 parameter Values. The first parameter ‘DatePart’ specifies, the part of the date, we want to use. The second parameter is the Actual Date from which we want to capture the part of the Date.

Date Functions in SQL with Practical Examples

Here is the Date Part Parameter Values Captured in The Below Table.

Examples:

• Day Example: Select DATENAME(Day, ‘2020-09-19 12:43:46.837’) – Returns 19
• WEEKDAY Example Select DATENAME(WEEKDAY, ‘2020-09-19 12:43:46.837’) – Returns Saturday
• MONTH Example Select DATENAME(MONTH, ‘2020-10-30 12:43:46.837’) – Returns October

A simple practical example using some of these Date Time functions. Consider the Table Employee from the Employee Database.

Write a query, to capture Name, Date Of Birth, Day, Month Name, Month Number and Year as shown in the below table.

Select Name, DateOfBirth, DateName(WEEKDAY,Date Of Birth) as [Day],
Month(Date Of Birth) as MonthNumber,
DateName(MONTH, Date Of Birth) as [MonthName],
Year(Date Of Birth) as [Year]
From Employees

Now Let Us See How to Write SQL Queries to Get People Born On Given Date Using Date Functions

Here I am going to use a table called People for all these examples. Current System Date is 2020-05-13

How to Write a SQL Query to get all People Born on 2019-10-09?

Select Name, DateOfBirth, CAST(DateOfBirth as Date) as [DatePart]
From People
Where CAST(DateOfBirth as Date) = '2019-10-09'

Row Number	Name	Date Of Birth
1	Raj	2019-10-09

How to Write a SQL Query to Get all People Born Between Two Given Dates? (Example 09 October 2019 and 01 September 2019)

Select Name, DateOfBirth, CAST(DateOfBirth as Date) as [DatePart]
From People
Where CAST(DateOfBirth) Between '2019-10-09' AND '2019-11-01'

Row Number	Name	Date Of Birth
1	Raj	2019-10-09
2	Siva	2019-11-01

How to Write a SQL Query to get all people born on the same day and month excluding the year (9th October)

Select Name, DateOfBirth, CAST(DateOfBirth as Date) as [DatePart]
From People
Where Day(DateOfBirth)=9 AND Month(DateOfBirth)=10

Row Number	Name	Date Of Birth
1	Raj	2019-10-09
2	Siva	2019-11-01

How to Write a SQL Query to get all people whose birth year is the same ( 2019)

Select Name, DateOfBirth, CAST(DateOfBirth as Date) as [DatePart]
From People
where Year(DateOfBirth)='2019'

Row Number	Name	Date Of Birth
1	Raj	2019-10-09
2	Siva	2019-11-01
3	Kumar	2019-04-12

How to Write a SQL Query to get all people born on Yesterday?

Select (GETDATE) Will give us the current Date with the timestamp

To Avoid timestamp use CAST Function

Select CAST(GETDATE() as Date)

Query

Select DATEADD(Day ,-1,CAST(GETDATE() as Date)) as [Datepart]
From People
Where CAST(DateOfBirth as Date) = DATEADD(Day ,-1, CAST(GETDATE() as DATE)

Row Number	Name	Date Of Birth
1	Rohan	2020-05-12

How to Write a SQL Query to get all people born Tomorrow?

Select DATEADD(Day,-1,CAST(GETDATE() as Date)) as [Datepart]
From People
Where CAST(DateOfBirth as Date) = DATEADD(Day,1, CAST(GETDATE() as DATE)

Row Number	Name	Date Of Birth
1	Jancy	2020-05-14

How to Write a SQL Query to get all people born between Yesterday and Today?

Select Name, DateOfBirth, CAST(DateOfBirth as Date) as [DatePart]
From People
Where CAST(DateOfBirth as Date)
Between
DATEADD (Day,-1, CAST(GETDATE() as Date)) AND CAST(GETDATE() as Date)

Row Number	Name	Date Of Birth
1	Kumar	2019-04-12
2	Divya	2020-05-13

How to Write a Query to capture people born last seven days excluding Current Date

Select Name, DateOfBirth, CAST(DateOfBirth as Date) as [DatePart]
From People
Where CAST(DateOfBirth as Date)
Between
DATEADD (Day,-7, CAST(GETDATE() as Date)) AND DATEADD (Day,-1, CAST(GETDATE() as Date))

Row Number	Name	Date Of Birth
1	Rohan	2020-05-12

This is all about Date Functions in SQL. I hope this article is useful to you. Please leave your comment in the comment section below.

Here you can check the related article Different Types of SQL Commands