Solved

getting month from a date type column

Posted on 2007-11-16
13
8,955 Views
Last Modified: 2013-12-07
Hello all.  I have a query where the user can specify the month and year of the data.  These will be two separate parameters.  I have tried all kinds of ways to do this.  This is one of many attempts that are not working. (see below)  Why does the first query not return anything when I have data in there but the second query returns data.

SELECT  *
FROM transactions
WHERE to_char(transactions.datetime,'Month') = 'November';


SELECT  to_char(transactions.datetime,'Month')
FROM transactions


Column has data like:

16-NOV-07

So I am not understanding the difference here.  


Also this will go into oracle reports and I will have to display the user para month as NOVEMBER.

Nothing is working.  I can get the query to work right two different ways but when I add it to the report it ill not work.  Please help.
0
Comment
Question by:dabrat
13 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20300542
p_month and p_year are the user-input parameters below.
SELECT *

FROM transactions

WHERE TO_CHAR(datetime,'MONTH') = UPPER(p_month)

AND TO_CHAR(datetime,'YYYY') = UPPER(p_year);

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20300549
Column has data like:

16-NOV-07

Does that mean datetime is not in fact a "date" but rather,
a string formatted with dd-MON-yy content?

A date doesn't have a format,  only the display of a date has a format.

So, if your datetime column is a string, you're getting an implicit conversion to a date, then converted back to a string by your to_char.


what are the outputs of your two queries?

what happens if you do this instead?  

SELECT  *
FROM transactions
WHERE to_char(transactions.datetime,'mm') = '11';

I'll bet that will work.

Also, try this...

SELECT  *
FROM transactions
WHERE trim(to_char(transactions.datetime,'Month')) = 'November';

because to_char with the "Month" and "Day" formats right pad the strings to a consistent length.  in the case of Month,  it's 9 characters, but November is only 8 characters in length.


0
 
LVL 1

Accepted Solution

by:
dabrat earned 0 total points
ID: 20300574
I replaced the MONTH with FMMONTH to suppress the trailing blank.

Thanks anyway guys!  And yes the datatime column is a DATA data type
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20300763
yes fmmonth  or trim month,  same idea, my personal preference is to use numerics that way you avoid spelling issues if you happen to change your nls settings
0
 
LVL 1

Author Comment

by:dabrat
ID: 20301440
Yes I understand your preface but the report requirement explicitly states uses will enter month like November not 11.  I wish it was 11 would be easier for me!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20302984
The answer was given.  The author also realized the problem (trailing spaces on the month strings) but I posted a solution to the problem as well as alternates solutions for other potential problems.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:dabrat
ID: 20313880
I did not see sdstuber's post to my question until after I posted that I found the solution.  I did not use his solution at all.  I would be happy to give him half the points however.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20313936
If I wasn't helpful at all then don't give any points.

If I at least pointed you in the right direction (trailing spaces) give me a B or C.
0
 
LVL 1

Author Comment

by:dabrat
ID: 20313953
You did mention trailing spaces but I had already figured that out before I read your post.  That is what I said in my previous post.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20314047
oh I thought you meant you didn't use my "trim" solution but you figured out the FM format because I pointed out the trailing spaces

I'll leave it to the moderator then to decide what to do.

Glad you found the answer!
0
 
LVL 1

Author Comment

by:dabrat
ID: 20314162
No sorry, I need to learn to investigate my issue a bit more before asking for help.  I have two questions where I asked for help and then found my solution shorty after.  Thanks again.
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20319586
Closed, 75 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

760 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