How to Find Various Day, Current Week, Two Week, Month, Quarter, Half Year and Year in SQL Server

Table of Contents

• Day
• Current Week Start Date And End Date
• Two Week Start and End Date
• Month Start and End Date
• Quarter Start and End Date
• Half Year Start and End Date
• Year Start and End Date
• Observations

Introduction

This tip provides an all in one collection which will be extremely helpful for almost all programmers in the current market who are using SQL server for their projects.

Background

I was working on one of the financial projects, one of my own custom implementations for SQL Server. I found something extremely important which is needed by most applications which stand in today’s market, henceforth thought of publishing a tip on the same. This will be needed for almost all financial applications that stand in today’s market and will be extremely important as it has wide range of applications in financial, retails, etc.

Using the Code

Finding Current Date

This is an extremely simple one and is mostly needed for beginners.

select GETDATE() 

Gets the current date from SQL Server.

Output

2013-07-27 14:45:44.463

Finding Start Date and End Date of the Week

The following will give start date of the current week. Assume the Current Date is 27^th July 2013.

select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

Output

2013-07-22 00:00:00.000

Finding End Date of the Week

select DATEADD(dd, 6-(DATEPART(dw,
GETDATE())), GETDATE())

Output

2013-07-26 14:51:36.1 

It is assumed that the beginning of the week is Monday and end is Friday, based on business day.

Finding Start Date and End Date of the Two Weeks

This part is pretty tricky as present day can be between first or second half and also the month may contain 28,29,30,31 days.

We will divide the date for 1-15 being first half, as used by most financial institutions and then based on where date falls, we compute the two weeks.

The following code provides beginning and end dates for two weeks:

if DAY(getdate()) <= 15
  begin
  select @beginDate = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
      select @endDate = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 14)
  end
  else
      begin
            select @beginDate = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 15)
      select @endDate = DATEADD(s,-1,DATEADD(mm,
DATEDIFF(m,0,GETDATE())+1,0))
  end

This will output 1-14 or 15-end of month as begin and end dates.

Finding Start Date and End Date of the Current Month

This part is pretty straight forward.

The following query provides start and end date of current month:

select @beginDate = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
select @endDate = DATEADD(s,-1,DATEADD(mm,
DATEDIFF(m,0,GETDATE())+1,0))

Finding Start Date and End Date of the Current Quarter

The following query provides start and end date of current month:

select @beginDate = DATEADD(q, DATEDIFF(q, 0, GETDATE()), 0)

select @endDate = DATEADD(d, -1, DATEADD(q,
DATEDIFF(q, 0, GETDATE()) + 1, 0))

Considering today date as 27^th July 2013.

The begin date will be:

2013-07-01 00:00:00.000

The end date will be:

2013-09-30 00:00:00.000

Finding Start Date and End Date For Half Year

This is quite a complicated part. We need to find date falls under first half or second half of the year and no direct methods available from SQL Server do the same.

The following query provides start and end dates for half year:

select @beginDate =
CAST(CAST(((((MONTH(GETDATE()) - 1) / 6) * 6) + 1) _
AS VARCHAR) + '-1-' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME);

select @endDate =
CAST(CAST(((((MONTH(GETDATE()) - 1) / 6) * 6) + 6) AS VARCHAR) + _

'-1-' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME);

Considering today's date as 27^th July 2013.

The begin date will be:

2013-07-01 00:00:00.000

The end date will be:

2013-12-01 00:00:00.000

Finding Start Date and End Date For Year

The following query finds start and end date for the current year:

select @beginDate =
dateadd(d,-datepart(dy,getdate())+1,getdate())
select @endDate =
dateadd(d,-datepart(d,getdate()),_
dateadd(m,13-datepart(m,getdate()),getdate()))

Considering today date as 27^th July 2013.

The begin date will be:

2013-01-01 15:15:47.097

The end date will be:

2013-12-31 15:15:47.113 

Observations

As some of the readers expressed their concerns, I thought it would be better to add observations. It may be noted that all the above code informs how to calculate the difference, i.e., how to find beginning date, end date for week, month, two weeks, etc..

The code has to be tweaked to meet the custom requirements like whether you want to include date part also in the calculations. In general, it may be noted that only date part is taken in this kind of computations and time part will be skipped.

How to get date with only date part for comparison:

lastupdatedate between convert(int, convert(varchar(10), @beginDate, 112))

and:

convert(int, convert(varchar(10), @endDate , 112))

This compares last update date with begin and end dates.