SELECT Statement for DATEPART, DATENAME with different caldendar year

Posted on 2007-07-24
Last Modified: 2013-11-16
I am running "SELECT" statement to output sales order year nad ordered date by quarterly using following statement:

SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
             'Q' + DATENAME(qq, OrderReceivedDate) AS OrderQtr

This results in following output:

1/1/2007 thru 3/31/2007 = (OrderQtr = Q1) and (OrderYear = 2007)
4/1/2007 thru  6/30/2007 = (OrderQtr = Q2) and (OrderYear = 2007)
and so on (In accouting term Quarter is broken by "Physical Year").

However, my client's Q1 does not start on Physical Year (1/1/2007) but rather starts on 10/1/2006. So 4 quarter for 2007 is following:

10/1/2006 - 12/31/2006 = Q1 for year 2007
1/1/2007 - 3/31/2007 = Q2 for year 2007
4/1/2007 -  6/30/2007 = Q3 for year 2007
7/1/2007 - 9/30/2007 = Q4 for year 2007

So my question is how can I run select statement using similar to below statement to accomplish above Calendar year which starts on 10/1/yyyy and ends on 9/30/yyyy?

SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
             'Q' + DATENAME(qq, OrderReceivedDate) AS OrderQtr

Thank you in advance,

Question by:CyberNerd
    LVL 3

    Expert Comment

    use datediff to take away 1 quarter from your results.  just make sure it's labeled the way you want it.
    LVL 3

    Expert Comment

    oops .. not datediff... dateadd.  use dateadd by quarter for -1 to take away a quarter.
    LVL 2

    Author Comment


    can you be more specific and wirte down the script?

    Thank you,

    LVL 3

    Accepted Solution

    sorry for the late reply (was off for 2 days).  SQL statements are not my best area but where you determine the quarter's start date, instead of 1-1-07 you need 10-1-06, correct?  all you'd have to do is take whatever statement that actually results in 1-1-07 and add this around it:

    dateadd("q", -1, <<your date statement>>)

    again, not being that great with SQL, i think it would look like this for you:

    SELECT DATEPART(yy, OrderReceivedDate) AS OrderYear,
                 'Q' + dateadd("q", -1, DATENAME(qq, OrderReceivedDate)) AS OrderQtr

    however, i must admit that i'm confused by the 'Q' + part of that statement.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now