Home » SQL & PL/SQL » SQL & PL/SQL » date conversion
date conversion [message #570560] Mon, 12 November 2012 05:15 Go to next message
MBISTATO
Messages: 7
Registered: November 2012
Junior Member
hi all!

I'm new member of this forum and I just get a small problem with pl/sql language. basically I have to insert in a date field a string that is a year(four characters).
The problem is that I have to convert a string in date obtaining just a year. How can i do this?
Re: date conversion [message #570563 is a reply to message #570560] Mon, 12 November 2012 06:03 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use TO_DATE function; specify appropriate format mask. If you have year only (4 characters), then it is YYYY. For example:
SQL> create table test (col date);

Table created.

SQL> declare
  2    l_year varchar2(4) := '2002';
  3  begin
  4    insert into test (col)
  5      values
  6      (to_date(l_year, 'yyyy'));
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select * from test;

COL
-------------------
01.11.2002 00:00:00

SQL>

Note that - by default - the result is set to the first day of a current month (while year remains intact).

[Updated on: Mon, 12 November 2012 06:05]

Report message to a moderator

Re: date conversion [message #570564 is a reply to message #570563] Mon, 12 November 2012 06:10 Go to previous messageGo to next message
MBISTATO
Messages: 7
Registered: November 2012
Junior Member
That is the problem! I want the fuction that return just the year and not the full date and time
Re: date conversion [message #570566 is a reply to message #570560] Mon, 12 November 2012 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A date contains ALL parts of the date including day, month and even time.
You have to be clearer in your requirements but if you just want to store a year then use an INTEGER datatype.

Regards
Michel
Re: date conversion [message #570567 is a reply to message #570564] Mon, 12 November 2012 06:17 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
Dates always have a full date and time. If you just have YYYY then you need to specify what you want the day, month and time to be set to.
Re: date conversion [message #570576 is a reply to message #570567] Mon, 12 November 2012 07:45 Go to previous messageGo to next message
MBISTATO
Messages: 7
Registered: November 2012
Junior Member
in my database I have a date field(birth of date) and another data field that is just the year. So can I extract the year from the birth of date keeping date as data type?
Re: date conversion [message #570577 is a reply to message #570576] Mon, 12 November 2012 07:53 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
Like this?
orcl> select extract (year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2012

orcl>


Re: date conversion [message #570578 is a reply to message #570577] Mon, 12 November 2012 07:56 Go to previous messageGo to next message
MBISTATO
Messages: 7
Registered: November 2012
Junior Member
but if I use that function the date formate has to be yyyy/mm/dd. what I have to do for converting it in dd/mm/yyyy?
Re: date conversion [message #570580 is a reply to message #570578] Mon, 12 November 2012 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you only keep the year what is the purpose to convert it to format DD/MM/YYYY (or whatever, it is not clear what you want)? DD/MM will always be 01/01 (or whatever you want).

Regards
Michel
Re: date conversion [message #570581 is a reply to message #570580] Mon, 12 November 2012 08:40 Go to previous messageGo to next message
MBISTATO
Messages: 7
Registered: November 2012
Junior Member
because the format date that I have is dd/mm/yyyy.
Re: date conversion [message #570583 is a reply to message #570581] Mon, 12 November 2012 08:53 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
Dates don't have a format. They are just dates. The format is only an issue for display. I'll extend my example:
orcl> select sysdate,
  2  to_char(sysdate,'dd/mm/yyyy'),
  3  to_char(sysdate,'yyyy/mm/dd'),
  4  extract (year from sysdate)
  5  from dual;

SYSDATE           TO_CHAR(SY TO_CHAR(SY EXTRACT(YEARFROMSYSDATE)
----------------- ---------- ---------- ------------------------
12-11-12 14:51:12 12/11/2012 2012/11/12                     2012

orcl>
you see? I happen to use a default display format of 'yy-mm-dd hh24:mi:ss' but that is of no significance to EXTRACT.
Re: date conversion [message #570586 is a reply to message #570577] Mon, 12 November 2012 09:06 Go to previous messageGo to next message
MBISTATO
Messages: 7
Registered: November 2012
Junior Member
sorry i didn't explain it well. Or course the dates have not format. I wrote the follow:

select extract(year from 19/12/2011) from dual;

and I got the error: invalid extract field for extract source. and if I write

select extract(year from date '19/12/2011') from dual;

literal does not match format string.

what I have to change in my code?
Re: date conversion [message #570589 is a reply to message #570586] Mon, 12 November 2012 09:08 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
Think, man. I used to_char to convert dates to strings. What function do you think might convert strings to dates?
Re: date conversion [message #570590 is a reply to message #570586] Mon, 12 November 2012 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 23188
Registered: January 2009
Senior Member
with oracle strings are enclosed in single quote marks.

>select extract(year from 19/12/2011) from dual;
above is 19 divided by 12 & that result is divided by 2011; since they are all NUMBERS
Re: date conversion [message #570591 is a reply to message #570590] Mon, 12 November 2012 09:21 Go to previous message
MBISTATO
Messages: 7
Registered: November 2012
Junior Member
THANK YOU. NOW IT IS CLEARER!
Previous Topic: DATABSE_LINK_ERROR
Next Topic: convert datetime to date
Goto Forum:
  


Current Time: Fri Dec 26 21:13:21 CST 2014

Total time taken to generate the page: 0.11551 seconds