What are The Date Functions in SQL With 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 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 are 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. The 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.
Wow, fantastic blog format! How long have you ever been blogging for? you make blogging look easy. The entire look of your website is fantastic, let alone the content material!