[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SELECT Statement for DATEPART, DATENAME with different caldendar year

Posted on 2007-07-24
5
Medium Priority
?
2,334 Views
Last Modified: 2013-11-16
Hi,
I am running "SELECT" statement to output sales order year nad ordered date by quarterly using following statement:

SET DATEFORMAT mdy
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?

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

Thank you in advance,

CN
0
Comment
Question by:CyberNerd
  • 3
4 Comments
 
LVL 3

Expert Comment

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

Expert Comment

by:kaosyeti
ID: 19556249
oops .. not datediff... dateadd.  use dateadd by quarter for -1 to take away a quarter.
0
 
LVL 2

Author Comment

by:CyberNerd
ID: 19556797
Kaosyeti,

can you be more specific and wirte down the script?

Thank you,

CN
0
 
LVL 3

Accepted Solution

by:
kaosyeti earned 1500 total points
ID: 19578757
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

831 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