How To Find Various Day, Current Week, Two Week, Month, Quarter, Half Year and Year In SQL Server
Date Computation
I was working on one of the financial projects on one of my own custom implementation for SQL Server. I found dates calculations to be extremely important which is needed by most of the applications which stand on today’s market, henceforth I thought of publishing an article on the dates topic. This will be needed for almost all financial applications that stands on today’s market and will be extremely important as it has wide range of applications in financial, Retails, etc. industries.
This article provides collection which will be extremely helpful for the programmers who are using SQL Server for their projects.* *
Finding Current Date
** **
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 Current Date is 27 th July 2013.
select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)
The output will be:
2013-07-22 00:00:00.000
Finding End Date of the Week
select DATEADD(dd, 6-(DATEPART(dw, GETDATE())), GETDATE())
The output will be:
2013-07-26 14:51:36.1
This is assumed that 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 MONTH(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
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 Quater
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 the 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 complicate part. We need to find date falls under first half or second half of the year and no direct methods available from sql server to 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 the today 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:
select @beginDate = dateadd(d,-datepart(dy,getdate())+1,getdate())
select @endDate = dateadd(d,-datepart(d,getdate()),dateadd(m,13-datepart(m,getdate()),getdate()))
Considering the 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
See Also
- [[SQL Server Query Language - Transact SQL]]