Irfan's World

January 24, 2011

SQL Server Dates – Part 2 (Quarter)

In this post, I will be showing you how to generate Quarterly dates using getdate() database function.

Quarter Dates

The following code will generate First Day of the Previous Quarter, Last Day of the Previous Quarter, First Day of the Current Quarter, Last Day of the Current Quarter.

select ‘First Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())-1,0)
select ‘Last Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)-1
select ‘First Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)
select ‘Last Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)-1
select ‘First Day of the Next Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)

Wait for my next post for more Quarterly dates. Click for Date Part 3

Advertisements

SQL Server Dates – Part 1 (Year)

To display today’s date in SQL Server 2008 select GETDATE().

Following are some of the dates that can be used in SQL Queries to fulfill the end user needs while retrieving data from the database or creating custom objects for delivering reports in BI tools.

select ‘First Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0)
select ‘Last Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)-1
select ‘First Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)
select ‘Last Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)-1
select ‘First Day of the Next Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)

I will be continuing on how to generate Quarterly dates in my next post Quarter Dates

January 23, 2011

SQL Server Dates – Part 3 (Quarter ..)

Quarterly Dates continued … Following are mostly used Quarterly Dates in Data Warehousing and Business Intelligence reports.

Previous Year Quarters:

select ‘First Day of the Previous Year / First Quarter :’ , DATEADD(Q,0,DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0))
select ‘First Day of the Previous Year / Second Quarter :’ , DATEADD(Q,1,DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0))
select ‘First Day of the Previous Year / Third Quarter :’ , DATEADD(Q,2,DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0))
select ‘First Day of the Previous Year / Fourth Quarter :’ , DATEADD(Q,3,DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0))

Current Year Quarters:

select ‘First Day of the Current Year / First Quarter :’ , DATEADD(Q,0,DATEADD(YY,DATEDIFF(YY,0,getdate()),0))
select ‘First Day of the Current Year / Second Quarter :’ , DATEADD(Q,1,DATEADD(YY,DATEDIFF(YY,0,getdate()),0))
select ‘First Day of the Current Year / Third Quarter :’ , DATEADD(Q,2,DATEADD(YY,DATEDIFF(YY,0,getdate()),0))
select ‘First Day of the Current Year / Fourth Quarter :’ , DATEADD(Q,3,DATEADD(YY,DATEDIFF(YY,0,getdate()),0))
select ‘First Day of the Current Year / Third Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,3,getdate())-1,0)
select ‘Last Day of the Current Year / Second Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,3,getdate())-1,0)-1

Blog at WordPress.com.