Solved

getting month from a date type column

Posted on 2007-11-16
13
8,956 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

895 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

18 Experts available now in Live!

Get 1:1 Help Now