Home » SQL & PL/SQL » SQL & PL/SQL » Extract Year and Month
Extract Year and Month [message #206393] Wed, 29 November 2006 21:42 Go to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
Hi

I am new to this forum.

I am having a requirement to extract Year and month from a given date format in Oracle

We have a column which is of Varchar Datatype and having values like 'JUN-02, JUL-01, AUG-99....'
I would like the output to be:
Year: 2002, 2001, 1999
Month: 6,7,8 (numbers)

I tried using to_date and to_char but did not get the answer

Plz give me suggestions how to accomplish this
Re: Extract Year and Month [message #206401 is a reply to message #206393] Wed, 29 November 2006 22:43 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member
Try to this for year as 2001,2002:-

Select to_char(sysdate,'yyyy') from dual;

And for month in digit, u can try it:-

Select decode(to_char(sysdate,'mon'),
'jan',1,'feb',2,'march',3,and so on upto 12) from dual;
Re: Extract Year and Month [message #206420 is a reply to message #206401] Thu, 30 November 2006 00:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No need for a decode!
SQL> select to_char(sysdate, 'MM') from dual;

TO
--
11


another way to extract year or month from a date:
SQL> select extract(year from sysdate) as year
  2  ,      extract(month from sysdate) as month
  3  from   dual;

      YEAR      MONTH
---------- ----------
      2006         11
Re: Extract Year and Month [message #206554 is a reply to message #206393] Thu, 30 November 2006 09:06 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Based on your example...
We have a column which is of Varchar Datatype and having values like 'JUN-02, JUL-01, AUG-99....'
I would like the output to be:
Year: 2002, 2001, 1999
Month: 6,7,8 (numbers)

you want the following SQL statement...

select to_char(to_date('JUN-02','MON-RR'),'MM  YYYY') from dual;
select to_char(to_date('JUL-01','MON-RR'),'MM  YYYY') from dual;
select to_char(to_date('AUG-99','MON-RR'),'MM  YYYY') from dual;


This will output the numeric month and year for you. You have to take the STRING that looks like your date and change it to an Oracle date format (the to_date part). Then, once you have the numeric date, change it back to a string with the format you want for your output (the to_char part).

ALWAYS REMEMBER THAT A DATE IN ORACLE IS A DECIMAL NUMBER, NOT A STRING OF NUMBERS AND CHARACTERS. YOU MUST USE to_char TO TRANSLATE AN ORACLE DATE INTO A STRING YOU'LL UNDERSTAND, AND, YOU MUST USE to_date TO TRANSLATE A DATE STRING INTO A DECIMAL NUMBER THAT ORACLE WILL UNDERSTAND!
Re: Extract Year and Month [message #206584 is a reply to message #206554] Thu, 30 November 2006 11:06 Go to previous messageGo to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
Hi

Thanks for the replies..
Now I am having a column name like
Column1 = 'JUN-06', 'AUG-99'....
Column1 is of varchar datatype
when I am trying the above commands witht he column name, there is no output

UPDATE tablename
SET YEAR= to_char(TO_DATE('Column1','MON-RR'),'YYYY')

I donot understand whats going wrong


Thanks

[Updated on: Thu, 30 November 2006 11:08]

Report message to a moderator

Re: Extract Year and Month [message #206586 is a reply to message #206584] Thu, 30 November 2006 11:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does it mean, "there is no output"? UPDATE doesn't produced any output (except a message 'n rows updated, or an error message). Did you get any of those? If not, WHAT did you get? "There is no output" isn't very descriptive.

Furthermore, it would be nice if you create a test case and post the whole SQL*Plus session so that we could see what you did. If you decide to do that, please, format your code using the [code] tags.
Re: Extract Year and Month [message #206587 is a reply to message #206393] Thu, 30 November 2006 11:16 Go to previous messageGo to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
I am sorry for not being clear. I am a beginner

It is not updating the corresponding fields
     
UPDATE tablename
SET YEAR= to_char(TO_DATE('Column1','MON-RR'),'YYYY')
  


When I executed the above SQL, it is highliting the Column1. I tried removing the quotes too. But there was no use.
Re: Extract Year and Month [message #206589 is a reply to message #206587] Thu, 30 November 2006 11:21 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
'Column1' gets highlighted when there's something wrong about it. Where do you run this statement? SQL*Plus or some other tool? No matter which one it is, it SHOULD return an error message (and not only highlight 'column1').

Can you follow this scenario and see will it help, or see what went wrong with your code?
SQL> create table test (column1 varchar2(10), col_date varchar2(4));

Table created.

SQL> insert into test (column1) values ('lip-06');

1 row created.

SQL> insert into test (column1) values ('kol-99');

1 row created.

SQL> update test set
  2    col_date = to_char(to_date(column1, 'mon-yy'), 'yyyy');

2 rows updated.

SQL> select * From test;

COLUMN1    COL_
---------- ----
lip-06     2006
kol-99     2099

SQL>


[EDIT] I forgot to mention: LIP and KOL are Croatian abbreviations for JUNE and AUGUST (and my database is set accordingly to it).

[Updated on: Thu, 30 November 2006 11:22]

Report message to a moderator

Re: Extract Year and Month [message #206590 is a reply to message #206393] Thu, 30 November 2006 11:29 Go to previous messageGo to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
I am using TOAD.
Column1
JUN-06
JUL-05
FEB-99
AUG-06
SEP-05


update Tablename set
    YEAR = to_char(to_date('Column1', 'mon-yy'), 'yyyy')


I do not know where I am going wrong

Re: Extract Year and Month [message #206594 is a reply to message #206590] Thu, 30 November 2006 12:11 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
remove the quotes around the column name. If it complains about it, then you don't have a column by that name in that table.

[Updated on: Thu, 30 November 2006 12:12]

Report message to a moderator

Re: Extract Year and Month [message #206599 is a reply to message #206393] Thu, 30 November 2006 13:28 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Your statement says take the literal string 'Column1', which is in the date format of 'mon-yy' and make it a date. 'Column1' is NOT a string that has the 'mon-yy' format.

update Tablename set
    YEAR = to_char(to_date('Column1', 'mon-yy'), 'yyyy')


Remove the quote marks which then tells the statement that the VALUE of Column1 should be translated into a date and the VALUE of Column1 has a format of 'mon-yy'.

You really should know the difference between the variable Column1 and the literal string 'Column1' and I recommend a review of the syntax for the to_date and to_char SQL functions.
Re: Extract Year and Month [message #206608 is a reply to message #206393] Thu, 30 November 2006 16:01 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
It could also be that he has a mixed case column name (shudder...), in that case then use

update Tablename set
YEAR = to_char(to_date("Column1", 'mon-yy'), 'yyyy');

Re: Extract Year and Month [message #206790 is a reply to message #206608] Fri, 01 December 2006 08:29 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Good point and just another case of how you screw yourself when you try to go against the Oracle standards and force mixed case or use reserved words for objects by putting them in double quotes at creation time.
Previous Topic: Tricky Trigger
Next Topic: Replication
Goto Forum:
  


Current Time: Sun Dec 11 08:30:11 CST 2016

Total time taken to generate the page: 0.15520 seconds