Click here to Skip to main content
15,867,453 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:
Please Sign up or sign in to vote.
4.57/5 (3 votes)
2 Aug 2013CPOL3 min read 72.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.

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.

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
select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

Output

2013-07-22 00:00:00.000

Finding End Date of the Week

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

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

SQL
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)
SQL
select @endDate = DATEADD(d, -1, DATEADD(q,
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 =
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 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


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

Comments and Discussions

 
BugReally u have to test the code before Posting... Pin
Raja Sekhar S31-Jul-13 22:07
Raja Sekhar S31-Jul-13 22:07 
GeneralRe: Really u have to test the code before Posting... Pin
KISHANHR1-Aug-13 11:00
KISHANHR1-Aug-13 11:00 
GeneralRe: Really u have to test the code before Posting... Pin
Raja Sekhar S1-Aug-13 21:20
Raja Sekhar S1-Aug-13 21:20 
GeneralRe: Really u have to test the code before Posting... Pin
KISHANHR2-Aug-13 0:00
KISHANHR2-Aug-13 0:00 
QuestionFinding Start Date and End Date For Year Pin
magic5630-Jul-13 23:00
professionalmagic5630-Jul-13 23:00 
AnswerRe: Finding Start Date and End Date For Year Pin
KISHANHR1-Aug-13 11:05
KISHANHR1-Aug-13 11:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.