• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8972
  • Last Modified:

getting month from a date type column

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
dabrat
Asked:
dabrat
1 Solution
 
Jinesh KamdarCommented:
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
 
sdstuberCommented:
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
 
dabratAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
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
 
dabratAuthor Commented:
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
 
sdstuberCommented:
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
 
dabratAuthor Commented:
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
 
sdstuberCommented:
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
 
dabratAuthor Commented:
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
 
sdstuberCommented:
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
 
dabratAuthor Commented:
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
 
Vee_ModCommented:
Closed, 75 points refunded.
Vee_Mod
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now