Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

getting month from a date type column

Posted on 2007-11-16
13
Medium Priority
?
8,965 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 74

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 74

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 74

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
 
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 74

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 74

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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

722 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