Home » SQL & PL/SQL » SQL & PL/SQL » problem in date conversion (merged)
icon11.gif  problem in date conversion (merged) [message #384413] Tue, 03 February 2009 14:51 Go to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
i have a table with month_id as number '2008410'
how can i convert to date format?

i tried like this

select to_date (to_char(month_id), 'YYYYQMM') from score

Doesnt work
Re: problem in date conversion [message #384416 is a reply to message #384413] Tue, 03 February 2009 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
do NOT post same problem more than once
Re: problem in date conversion [message #384417 is a reply to message #384413] Tue, 03 February 2009 15:02 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

>Doesnt work
100% useless statement; not a valid Oracle error code.

It works for me.
Re: problem in date conversion [message #384418 is a reply to message #384413] Tue, 03 February 2009 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
do NOT post same problem more than once
Re: problem in date conversion [message #384420 is a reply to message #384418] Tue, 03 February 2009 15:08 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
Its the issue with the website. Its not responding and
saying session expired . resubmit again
Re: problem in date conversion [message #384421 is a reply to message #384417] Tue, 03 February 2009 15:11 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
It works for me ?

Did you try

select to_date (to_char(2008410), 'YYYYQMM') from dual ;


gives me the below error.
ORA-01820: format code cannot appear in date input format


Re: problem in date conversion [message #384422 is a reply to message #384413] Tue, 03 February 2009 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
it works when TO_CHAR is used instead of (ab)using default datatype conversion
Re: problem in date conversion [message #384423 is a reply to message #384422] Tue, 03 February 2009 15:20 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
instead of (ab)using ?

What is ab?
Re: problem in date conversion [message #384424 is a reply to message #384413] Tue, 03 February 2009 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
using or abusing
Re: problem in date conversion [message #384427 is a reply to message #384424] Tue, 03 February 2009 16:14 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
select to_date (to_char(2008410), 'YYYYQMM') from dual ;

I stil didnt get you .It still doesnt work for me.
Re: problem in date conversion [message #384428 is a reply to message #384427] Tue, 03 February 2009 16:35 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Q is not a viable character in this context
Re: problem in date conversion [message #384431 is a reply to message #384428] Tue, 03 February 2009 16:59 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
then how the below works?
select to_char(sysdate, 'YYYYQMM') from dual
2009101
Re: problem in date conversion [message #384432 is a reply to message #384431] Tue, 03 February 2009 17:02 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
It works because you are performing a different task.
try

select to_date ('2008410', 'YYYYQmm') from dual;

you will get a similar error and that is closer to what you are trying to do.

[Updated on: Tue, 03 February 2009 17:04]

Report message to a moderator

Re: problem in date conversion (merged) [message #384433 is a reply to message #384413] Tue, 03 February 2009 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
select TO_CHAR(to_date (to_char(2008410), 'YYYYQMM'),'YYYYQMM') from dual ;
Re: problem in date conversion (merged) [message #384434 is a reply to message #384433] Tue, 03 February 2009 17:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Not sure whether you are making a joke that I'm not getting or whether you are maybe tired?
Re: problem in date conversion (merged) [message #384435 is a reply to message #384434] Tue, 03 February 2009 17:17 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
No Sir, i m not joking . The solution given by blackswan doesnt work .
select TO_CHAR(to_date (to_char(2008410), 'YYYYQMM'),'YYYYQMM') from dual ;

I m wondering .. what to do .
Re: problem in date conversion (merged) [message #384436 is a reply to message #384435] Tue, 03 February 2009 17:20 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
konark wrote on Tue, 03 February 2009 23:17
No Sir, i m not joking .

Please look at whom my reply (re joking) was directed at.
What to do... well, you cannot convert the value as is, you will have to somehow remove the Q portion of the number.
Re: problem in date conversion (merged) [message #384437 is a reply to message #384433] Tue, 03 February 2009 17:53 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
Blackswan,
You wrote a solution . Did you ever test if it works?

select TO_CHAR(to_date (to_char(2008410), 'YYYYQMM'),'YYYYQMM') from dual ;


[Updated on: Tue, 03 February 2009 17:54]

Report message to a moderator

Re: problem in date conversion (merged) [message #384461 is a reply to message #384413] Tue, 03 February 2009 23:42 Go to previous messageGo to next message
srinivasreddy777
Messages: 11
Registered: October 2007
Location: Hyderabad
Junior Member

Konark,

Try this:
SELECT to_date(TO_CHAR(SUBSTR('2008410',1,4)
|| SUBSTR('2008410',-2,2)),'YYYYMM')
FROM dual
Re: problem in date conversion (merged) [message #384474 is a reply to message #384461] Wed, 04 February 2009 00:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
@srinivasreddy777
Great that you posted the solution, unfortunately it means that konark will just take it and use it without really considering why his method didn't work in the first place and why the solution that you provided works. try by offering hints and samples rather than an almost complete solution.

[Updated on: Wed, 04 February 2009 00:30]

Report message to a moderator

Re: problem in date conversion (merged) [message #384585 is a reply to message #384413] Wed, 04 February 2009 06:13 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Try to look at your format. You are giving in 'YYYYQMM' as the format. What if you then were to give in the string '2009401' as the date string? This could obviously not be allowed. Putting both the quarter and the month in the format doesn't make sense. If you know the month you also know the quarter.
Re: problem in date conversion (merged) [message #384588 is a reply to message #384585] Wed, 04 February 2009 06:17 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
@c_stenersen ... So basically you are just summarising what has already been said. Or do you feel that your post actually added something new to the thread?
Re: problem in date conversion (merged) [message #384603 is a reply to message #384588] Wed, 04 February 2009 07:07 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Just adding a link for any readers to understand the Format masks that can be used in the To_Date Function.
Oracle to_date usage

Regards,
Jo
Re: problem in date conversion (merged) [message #384660 is a reply to message #384461] Wed, 04 February 2009 12:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
srinivasreddy777 wrote on Wed, 04 February 2009 00:42
Konark,

TO_CHAR(SUBSTR('2008410',1,4)...



Why would one do a TO_CHAR of a CHAR?
Re: problem in date conversion [message #384673 is a reply to message #384424] Wed, 04 February 2009 15:05 Go to previous messageGo to next message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member
BlackSwan wrote on Tue, 03 February 2009 13:25
using or abusing

it works when TO_CHAR is used instead of (ab)using default datatype conversion




odd, my daughter uses that as an IM text string ... thus another person violating the posting rules of no IM speak
Re: problem in date conversion [message #384675 is a reply to message #384673] Wed, 04 February 2009 15:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where is there IM speak in what you quoted?

Regards
Michel
Re: problem in date conversion [message #384743 is a reply to message #384673] Thu, 05 February 2009 00:41 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I use "isn't" as text speech. Doesn't make it IM speak.
Previous Topic: find special charecters in a column
Next Topic: SQL based on condition
Goto Forum:
  


Current Time: Fri Dec 02 16:21:18 CST 2016

Total time taken to generate the page: 0.36586 seconds