15,997,776 members
Articles / Programming Languages / SQL
Tip/Trick

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

Rate me:
2 Aug 2013CPOL3 min read 73.8K   9   6
Describes how to find various days, current week, two week, month, quarter, half year, and year in SQL Server which are required in most applications.

• 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.

SQL
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 27th July 2013.

SQL

#### Output

2013-07-22 00:00:00.000

### Finding End Date of the Week

SQL
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:

SQL
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)
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:

SQL
select @beginDate = DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
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)
SQL
DATEDIFF(q, 0, GETDATE()) + 1, 0))

Considering today date as 27th 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:

SQL
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 27th 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:

SQL
select @beginDate =
select @endDate =

Considering today date as 27th 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:

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

and:

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

This compares last update date with begin and end dates.

Written By
Architect Ministry of Qatar
United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

 First Prev Next
 Really u have to test the code before Posting... Raja Sekhar S31-Jul-13 22:07 Raja Sekhar S 31-Jul-13 22:07
 Re: Really u have to test the code before Posting... KISHANHR1-Aug-13 11:00 KISHANHR 1-Aug-13 11:00
 Re: Really u have to test the code before Posting... Raja Sekhar S1-Aug-13 21:20 Raja Sekhar S 1-Aug-13 21:20
 Re: Really u have to test the code before Posting... KISHANHR2-Aug-13 0:00 KISHANHR 2-Aug-13 0:00
 Finding Start Date and End Date For Year magic5630-Jul-13 23:00 magic56 30-Jul-13 23:00
 Re: Finding Start Date and End Date For Year KISHANHR1-Aug-13 11:05 KISHANHR 1-Aug-13 11:05
 Last Visit: 31-Dec-99 18:00     Last Update: 14-Sep-24 16:41 Refresh 1