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

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