Irfan's World

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

Advertisements

7 Comments »

  1. Thank you very much, you have solved my problems for SqlServer dates

    Comment by James mwangi — June 13, 2012 @ 9:11 am

  2. Thank you for your help! How to get the last day of first quarter for the current year?

    Comment by zel — November 14, 2012 @ 4:58 am

    • For SQL Server you can use the following, this should give you last day of the first quarter for current year.

      DATEADD(QQ,1,DATEADD(YYYY,DATEDIFF(YYYY,0,GETDATE()),0))-1

      Comment by Irfan — November 18, 2012 @ 1:08 pm

  3. how can i change from 20130124 that is stored in character to date as MM/dd/yyyy in designer.

    thanks.
    Ali.

    Comment by Ali Toor. — February 21, 2013 @ 1:08 pm

    • You may use CONVERT or CAST.

      Comment by Irfan — February 24, 2013 @ 9:22 am

  4. You actually make it appear so easy along with your presentation but I in finding this topic to
    be actually something that I feel I might by no means understand.
    It kind of feels too complicated and very large for me.

    I’m having a look ahead on your next submit, I’ll attempt to get the cling of it!

    Comment by maryland website company facts — April 9, 2013 @ 10:04 pm

  5. How to get the last week (full week) from Monday to Friday based on whatever day we are in this week in IDT?

    Comment by Avishek — July 16, 2014 @ 1:28 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: