14,977,142 members
Articles / Programming Languages / SQL
Tip/Trick
Posted 30 Jul 2013

69K views
9 bookmarked

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

Rate me:
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
`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)
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
```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 =
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.

Share

 Architect Ministry of Qatar United States
No Biography provided

 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
 Thanks for your comment, I had copied the code which was used for some other purpose which i had built, Most of the code i have executed and have copied the output in the above article also... There the main observation would be Copy Code `DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 15)` for the readers, anyhow i will add that for user to get better picture....
 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: 29-Jul-21 22:07 Refresh 1