How to query records covered by quarter date

Question

3.00/5 (2 votes)

See more:

Goodday! I have this form where the user/encoder can select agency and its project, and a combobox which he has to select if First, Second, Third, Fourth Quarter of the year. When First is selected, all transactions of that agency should be listed from January to March.

SQL

SELECT ctrl_no, a_code, prj_code
FROM tbltrans
where date_created >=('1 January 2012')
and date_created <= ('31 March 2012')

Thanks!

Posted 25-Nov-12 8:16am

Member 9372112

Add a Solution

Comments

André Kraak 25-Nov-12 14:19pm

Please specify the properties of the date_created field.
Is it a text field?

Jörgen Andersson 25-Nov-12 15:08pm

And please specify the database used.

Member 9372112 25-Nov-12 15:42pm

I am using ORACLE 10g xe

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Answer 1 · 2012-11-25T08:26:00

Solution 1

Before you can do anything you need to convert the text to a date using TO_DATE[^].
With the date you can use TO_CHAR[^] to get the quarter in which the date belongs.
Both functions use Datetime Format Elements[^].

SQL

SELECT ctrl_no, a_code, prj_code
FROM tbltrans
WHERE TO_CHAR( TO_DATE( date_created, 'DD Month YYYY', 'nls_date_language = us' ), 'Q') = '1'

Posted 25-Nov-12 8:26am

André Kraak

Updated 25-Nov-12 21:18pm

v3

Comments

Member 9372112 25-Nov-12 15:40pm

Thanks for your reply.
But i stored it as varchar.

André Kraak 25-Nov-12 16:26pm

I altered the solution for your situation.

Member 9372112 25-Nov-12 22:40pm

Hi sir. I tried this but im getting this error :
ORA-12702: invalid NLS parameter string used in SQL function
Sorry to bother you.

André Kraak 26-Nov-12 3:19am

I made a error in specifying the NLS parameter, how it is used is defined here http://docs.oracle.com/cd/B14117_01/server.101/b10749/ch9sql.htm#sthref1105[^].

I corrected the answer.

Member 9372112 30-Nov-12 20:05pm

I have figured out this one. What i input is 1, 2, 3 or 4. What i want to see is the transactions covered from that quarter. For example, i input 4, all records dated november showed but january is also included. i want to exclude that.

Herman<T>.Instance · Answer 2 · 2012-11-25T21:24:00

Solution 2

use DatePart function. See here[^]

Posted 25-Nov-12 21:24pm

Herman<T>.Instance

Comments

André Kraak 26-Nov-12 3:34am

Unfortunately DatePart is not available in ORACLE 10g, the database the OP is using (as specified in the comments).