I have a query that puts dates into quarters ofr me. The problem is that it puts it into jan-mar as quarter one. I need jul-sep of 2008 to be q1 2009 and jan-mar to be q3 2009. Any ideas?
Mar 31 '09
19  6331  ADezii 8,834
Recognized Expert Expert @NeoPa
You mean we did all that work for nothing? (LOL).
@ADezii
I doubt it was for nothing, at least this way I have multiple ways to try. I am having a long weekend from work so I won't be working on this till Monday, but I will let you know how it goes. Thanks for all the feedback, it all helps.
NeoPa 32,585
Recognized Expert Moderator MVP
I certainly hope so ADezii :D
But in truth, I think RCollins has the right idea. All ideas are worth exploring.
Not quite working, NeoPa (one quarter out in the changeovers), but it's a nice simplification. Applied to the same testdata as above: - Date Quarter Test
-
01/07/2008 2009-Q1 q1 2009
-
01/08/2008 2009-Q1 q1 2009
-
01/09/2008 2009-Q1 q2 2009
-
01/10/2008 2009-Q2 q2 2009
-
01/11/2008 2009-Q2 q2 2009
-
01/12/2008 2009-Q2 q3 2009
-
01/01/2009 2009-Q3 q3 2009
-
01/02/2009 2009-Q3 q3 2009
-
01/03/2009 2009-Q3 q4 2009
-
01/04/2009 2009-Q4 q4 2009
-
01/05/2009 2009-Q4 q4 2009
-
01/06/2009 2009-Q4 q1 2010
-
01/07/2009 2010-Q1 q1 2010
-
01/08/2009 2010-Q1 q1 2010
-
01/09/2009 2010-Q1 q2 2010
-
01/10/2009 2010-Q2 q2 2010
-
01/11/2009 2010-Q2 q2 2010
-
01/12/2009 2010-Q2 q3 2010
-
01/01/2010 2010-Q3 q3 2010
-
01/02/2010 2010-Q3 q3 2010
-
01/03/2010 2010-Q3 q4 2010
-
01/04/2010 2010-Q4 q4 2010
-
01/05/2010 2010-Q4 q4 2010
-
01/06/2010 2010-Q4 q1 2011
-
01/07/2010 2011-Q1 q1 2011
-
01/08/2010 2011-Q1 q1 2011
-
01/09/2010 2011-Q1 q2 2011
-
01/10/2010 2011-Q2 q2 2011
-
01/11/2010 2011-Q2 q2 2011
-
01/12/2010 2011-Q2 q3 2011
-Stewart
...but with the following slight tweak - Format(DateAdd("m", 6, [somedate]), "yyyy-\Qq")
it gives... - Date Quarter Test
-
01/07/2008 2009-Q1 2009-Q1
-
01/08/2008 2009-Q1 2009-Q1
-
01/09/2008 2009-Q1 2009-Q1
-
01/10/2008 2009-Q2 2009-Q2
-
01/11/2008 2009-Q2 2009-Q2
-
01/12/2008 2009-Q2 2009-Q2
-
01/01/2009 2009-Q3 2009-Q3
-
01/02/2009 2009-Q3 2009-Q3
-
01/03/2009 2009-Q3 2009-Q3
-
01/04/2009 2009-Q4 2009-Q4
-
01/05/2009 2009-Q4 2009-Q4
-
01/06/2009 2009-Q4 2009-Q4
-
01/07/2009 2010-Q1 2010-Q1
-
01/08/2009 2010-Q1 2010-Q1
-
01/09/2009 2010-Q1 2010-Q1
-
01/10/2009 2010-Q2 2010-Q2
-
01/11/2009 2010-Q2 2010-Q2
-
01/12/2009 2010-Q2 2010-Q2
-
01/01/2010 2010-Q3 2010-Q3
-
01/02/2010 2010-Q3 2010-Q3
-
01/03/2010 2010-Q3 2010-Q3
-
01/04/2010 2010-Q4 2010-Q4
-
01/05/2010 2010-Q4 2010-Q4
-
01/06/2010 2010-Q4 2010-Q4
-
01/07/2010 2011-Q1 2011-Q1
-
01/08/2010 2011-Q1 2011-Q1
-
01/09/2010 2011-Q1 2011-Q1
-
01/10/2010 2011-Q2 2011-Q2
-
01/11/2010 2011-Q2 2011-Q2
-
01/12/2010 2011-Q2 2011-Q2
-Stewart
NeoPa 32,585
Recognized Expert Moderator MVP @Stewart Ross Inverness
You're right Stewart. I was sloppy with my arithmetic. I must admit that I was focusing my attention on the concept rather than the implementation, and rushed it out.
Hi NeoPa. Your solution is a great example of using the built-in facilities (DateAdd and Format in this case) to the full before trying bespoke programming. It is commendably simple! I just wonder why I didn't think of it instead (shakes head...)
Cheers
Stewart
NeoPa 32,585
Recognized Expert Moderator MVP
Thanks for that Stewart. I just wish I hadn't spoiled the effect with the nooby arithmetic ;)
ADezii 8,834
Recognized Expert Expert @Stewart Ross Inverness
Hey Stewart, I always said that NeoPa was commendably simple! I am referring to his programming skills, of course (LOL)!
NeoPa 32,585
Recognized Expert Moderator MVP
Very nice ADezii. I actually laughed out loud on that one.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: bp |
last post by:
I'm using the System.Globalization.GregorianCalendar class to calculate
week numbers. My weeks begin on Saturday and follow the "first four
day" rule.
GregorianCalendar cal = new GregorianCalendar();
week = cal.GetWeekOfYear("12/31/2005",
CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Saturday);
GetWeekOfYear() returns "53" for "12/31/2005", even though there are
only 52 weeks in 2005. "12/30/2005" correctly returns "52", and
|
by: charliewest |
last post by:
Can someone recommend a fairly easy to use ASP.NET custom calendar control
that makes it easy for the user to select the month and year - versus the
inherent "paging" that is used in the default Web Control?
For example, when i need to select the DOB (April 4, 1970), i need to page
"left" endlessly....
I have found some controls on teh Web, but my programming level is
intermediate, and i have not been able to get any of them to work...
|
by: ajmera.puneet |
last post by:
If I have Calendar Control on Asp.net page and I have a table for
Fiscal years on sql server
then, How can I check the dates from table to Calendar Control,so that
I can format the Calendar control cells according to my need.
I want to change the color of Dates according to fiscal month. i.e. As
Calendar control has dates for Current + next + Previous months, I need
to change the color of dates of month which is comes under Fiscal...
|
by: rkohon |
last post by:
Hello all,
I am new to JavaScript and need some ideas, suggestions, or code snippets.
I have a form which requires the end user to put in a date for required items. I need javascript function to run on this date supplied by end user and for it to populate another area of the form with the Fiscal Year that this will be needed in.
Example: User populates form field with 25-SEPT-07 we need the the Quarter required to immediately poplate...
|
by: Twobridge |
last post by:
Hi
I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this
Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
| | |
by: Sund via AccessMonster.com |
last post by:
I do fair amount of data analysis using access pivot tables and charts. Can
any body suggest a method to run the queries based on accounting month and
Accounting year.As an example: I want to analyze sales order per month and
per Quarter. Our fiscal First quarter starts at 1st April with start date as
April 1st and End date as 29 April ( Each month ends at Last Sunday of that
month)
I appreciate any comments.
Thanks in advance
|
by: craigfr |
last post by:
I am making a graph comparing last year's defect data with YTD defect data. Our fiscal year starts Nov.1 and ends Oct.31.
To get the YTD, I started used a simple date serial criteria:
Between DateSerial(Year(Date())-1,11,1) And Date()
This works, but only for 10 months out of the year. If your'e in December, it will calculate the YTD from last year's November to the current December, not the last November.
My solution was to make an...
|
by: Lars Eighner |
last post by:
Is a calendar tabular data, logically meriting table markup?
--
Lars Eighner <http://larseighner.com/ <http://myspace.com/larseighner>
Countdown: 465 days to go.
What do you do when you're debranded?
|
by: mathewgk80 |
last post by:
HI all,
I am having popup calendar Javascript code. But i dont know how it is connecting to asp.net code.. I am using asp.net,c#.net and also using 3tier architecture with master page....
I would like to get the code for connecting the javascript to asp.net page...
Please help me...
The javascript code is as follows..
|
by: abhishekbrave |
last post by:
The code below is opening a calendar on mouse over in the same window.
I need the calendar to be opened in new window.
Have to fulfill this requirement urgentely so posting the whole code here.
I tried doing some workaround using window.open() but not getting the calendar in new window.
<html>
<head>
<script language="JavaScript">
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
