Solved

getting month from a date type column

Posted on 2007-11-16
13
8,959 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 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

856 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