SQL Server Datetime functions examples

In this SQL Server tutorial, we will discuss some examples related to the DateTime data type in SQL Server. We will also discuss some SQL Server DateTime functions that you can use to get the date and time information.

• SQL Server first day of month
• SQL Server last day of month
• Get current month start date and end date in SQL Server
• How to check current date is between startdate and enddate in SQL Server?
• How to get the start and end dates of all weeks between two dates in SQL Server?
• Get all dates of given month and year in SQL Server
• SQL query to get data for a particular date
• SQL Server count records between dates

SQL Server first day of month

In some scenarios, you may need to find the first day of a month from a DateTime variable in SQL Server.

For example, if the DateTime in a column value is ‘2021-12-29 07:12:45.260’, the current month is 12 and the first day of the month will be ‘2021-12-01’.

Let me show you how you can do this. There are various ways to do this. The first way is to use the below SQL query:

DECLARE @date DateTime = '2021-12-29 07:12:45.260'
SELECT CAST(CAST(YEAR(@date) AS VARCHAR(4)) 
+ '/' + CAST(MONTH(@date) AS VARCHAR(2)) + '/01' AS DATE) AS FirstDayOfMonth

In the above query, I have fetched the year and month from the given DateTime value and appended 01 i.e. the first day of the month, and cast the resulting string into a Date type value. You can also use the DateTime data type if you want the full DateTime value.

Alternatively, you can also use one of the below T-SQL scripts to find the first day of the month:

DECLARE @date DateTime = '2021-12-29 07:12:45.260'
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0) AS FirstDayOfMonth

DECLARE @date DateTime = '2021-12-29 07:12:45.260'
SELECT DATEFROMPARTS(YEAR(@date),MONTH(@date),1)

These were the ways that you can use to find the first day of the month from a given DateTime value.

Also, check: SQL Server DateTime vs Datetime2

SQL Server last day of month

Noe let us also find the last day of the month from a specified date. You can use the EOMONTH() function to find the last day of a month. The function takes two arguments

• The date value from which we will find the last day of the month
• The offset value i.e. an optional integer value that will be added to the given month value

Now let us see how to use it. Suppose, I have a Date data type value as 2021-12-15. We can find the last day of this month using the below query:

DECLARE @date DateTime = '2021-12-15'
SELECT EOMONTH(@date) AS LastDayOfMonth

The good thing about the EOMONTH is that if you pass a date having with the month as February of a leap year, you will get accurate results. For example, let us pass the date 2020-02-12 to the EOMONTH() function and see the result.

DECLARE @date DateTime = '2020-02-12'
SELECT EOMONTH(@date) AS LastDayOfMonth

You can see that we got the correct last day of the February month of a leap year. In this way, you can find the last day of the month using a simple function.

Read: SQL Server Convert Datetime to String

Get current month start date and end date in SQL Server

Till now we have discussed how to get the start date and end date of a month in SQL Server using various methods. Now let us see how we can get the start date and end date of the current month.

You can use the below SQL query to find the start date and end date of the current month in SQL Server:

DECLARE @date DateTime = GETDATE()
SELECT DATEFROMPARTS(YEAR(@date),MONTH(@date),1) AS [First Day of Month],
EOMONTH(@date) AS [Last Day Of Month]

In the above query, I have used the GETDATE() function to get the current date. To find the first day and the last day of the month, I have used the methods that I have explained in the above sections.

Read: SQL Server Replace Function

How to check current date is between startdate and enddate in SQL Server?

You can use the BETWEEN operator to find whether a date value lies between two date values or not. I have explained this operator along with other logical operators in the SQL Server logical operators and example blog.

We have also discussed how we can get the current date using the GETDATE() function. Now let us see how we can use the BETWEEN operator and the GETDATE() function to find if a Date value lies between two Date values or not.

Look at the SQL code below:

DECLARE @date Date = GETDATE()
IF @date BETWEEN '2019-02-11' AND '2022-01-23'
	PRINT(' The current date lies between the specified range')
ELSE
	PRINT(' The current date does not lie between the specified range')

IF @date BETWEEN '2019-02-11' AND '2020-01-23'
	PRINT(' The current date lies between the specified range')
ELSE
	PRINT(' The current date does not lie between the specified range')

I have used two IF-ELSE blocks and specified two different date ranges. You can see how I used the BETWEEN operator to check if the current date lies between the specified ranges or not.

Thus, you might have learned how to check the current date is between the start date and the end date in SQL Server.

Read: SQL Server Convert Function

How to get the start and end dates of all weeks between two dates in SQL Server?

If you want to get the start and end dates of all weeks between two dates in SQL Server, there can be multiple solutions. One of the solutions is given below:

DECLARE @StartDate DateTime,
        @EndDate DateTime;

SELECT  @StartDate = '2020-04-25',
        @EndDate = '2020-06-25';
;WITH CTE AS
(
  SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartDate), 0) AS StartDate, DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartDate), 6) AS EndDate
  UNION ALL
  SELECT DATEADD(WEEK, 1, StartDate), DATEADD(WEEK, 1, EndDate)
  FROM CTE
  WHERE DATEADD(WEEK, 1, StartDate) <= @EndDate
)
SELECT * FROM CTE

• In the above query, we are using a Common Table Expression(CTE) so that we can create a temporary resultset.
• We are creating a temporary resultset for the first week start and end dates.
• Later we are generating the start and end dates of the upcoming weeks on the basis of this temporary resultset by adding 1 week everytime using the DATEADD() function till we reach the specified last date.
• The final result is the temprary resultset having the start dates and end dates of all the weeks between the specified date range.
• Lastly, we have retrieved data from this temporary resultset using the SELECT statement and the output is as follows:

Hence, in this way, you can get the start and end dates of all weeks between two dates in SQL Server.

Read: SQL Server Convert Datetime to date

Get all dates of given month and year in SQL Server

In this section, I will explain how to get all the dates of a given month and year in SQL Server. There can be multiple solutions to this problem. I will explain one of the methods to do this.

• We will use the same approach that we used in the above example i.e. use a Common Table Expression. Look at the SQL code below:

DECLARE 
	@year int = 2019,
	@month int = 2;
WITH numbers
AS
(
    SELECT 1 AS VALUE
    UNION ALL
    SELECT VALUE + 1 FROM numbers
    WHERE VALUE + 1 <= DAY(EOMONTH(DATEFROMPARTS(@year,@month,1)))
)
SELECT DATEFROMPARTS(@year,@month,numbers.VALUE) AS Dates FROM numbers

• In this example also, we will also create a temporary resultset.
• Firstly, we are iterating the date from 1 to the last day of the month.
• The EOMONTH() function returns the last day of the month.
• The DATEFROMPARTS() function is used to create a DateTime data type value so that we can pass it to the EOMONTH() function.
• At the end, we got a resultset containing dates from the first day of the month to the last day of the month. You can see the output in the below image:

Thus, you might have learned how to get all dates of a given month and year in SQL Server.

Read: SQL Server Convert String to Date

SQL query to get data for a particular date

In this section, I will create an example to demonstrate how you can get data from a table for a particular date. I have created a sample Products table where I have stored the list of products along with their manufacturing dates.

Look at the below query:

USE master
GO
SELECT * FROM dbo.Products WHERE [Mfg Date] = '2020-12-12'

• In the above query that I have passed the date value in the WHERE class.
• You can see that I got the record having the Mfg Date that was specified.
• The date value specified is needed to be enclosed between quotes.
• Also, you can see the format of date I have used.

In this way, you can get data for a particular date from a table in SQL Server.

Read: SQL Server Substring Function

SQL Server count records between dates

In this section, I will explain how you can get the count of records having a date value between a range of date values. I will use the same table that I used in the above section i.e. dbo.Products.

Suppose I want to get the count of products having a manufacturing date between a specified date range. In that case, I can use the BETWEEN operator and specify the date range. For example, look at the below SELECT query:

USE master
GO
SELECT COUNT([Product ID]) AS [Number of Products]
FROM dbo.Products WHERE [Mfg Date] BETWEEN '2019-01-01' AND '2020-12-12'

• To get the count of records, I have used the COUNT() function. In the output below, you can see that I got the count of products manufactured between the specified date range.

Hence, in this way, you can get the count of records between a specified date range in SQL Server.

You may also like to read the following tutorials on SQL Server.

• SQL Server Date Format 
• SQL Server Trigger Update
• SQL Server Trigger Before Update
• Exception Handling in SQL Server
• SQL Server INSERT INTO SELECT
• Types of Backup in SQL Server
• SQL Server Trigger After Insert Update
• SQL Server Convert integer to string

Thus, in this article, we saw some scenarios that are commonly encountered while working with the SQL Server Datetime functions.

• SQL Server first day of month
• SQL Server last day of month
• Get current month start date and end date in SQL Server
• How to check current date is between startdate and enddate in SQL Server?
• How to get the start and end dates of all weeks between two dates in SQL Server?
• Get all dates of given month and year in SQL Server
• SQL query to get data for a particular date
• SQL Server count records between dates