Home » SQL & PL/SQL » SQL & PL/SQL » number to date format ?
number to date format ? [message #621169] Mon, 11 August 2014 01:03 Go to next message
tondapi
Messages: 98
Registered: August 2007
Location: usa
Member
Hi,

In my table 'calender', I have a column called 'Year_Mon_value' and data is like '201404,201405,2014,06,201407...etc').
How to bring my query to display the data in below format.
2014 Apr
2014 Jun
2014 Jul....

Note : Year_mon_value is number formate

Thanks,
Re: number to date format ? [message #621170 is a reply to message #621169] Mon, 11 August 2014 01:07 Go to previous messageGo to next message
Littlefoot
Messages: 19649
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option, which requires two conversions: the original NUMBER value into a DATE, and then DATE into CHARACTER with the desired format mask.
SQL> with test as (select 201404 col from dual union
  2                select 201407 col from dual
  3               )
  4  select to_char(to_date(col, 'yyyymm'), 'yyyy Mon') result
  5  from test;

RESULT
--------
2014 Apr
2014 Jul

SQL>
Re: number to date format ? [message #621171 is a reply to message #621169] Mon, 11 August 2014 01:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2407
Registered: May 2013
Location: World Wide on the Web
Senior Member
It is a bad practise to have a date column as number type. The permanent solution is to change the data type.

In your case, convert the number to char, then use to date, then to char and use proper date format mask to achieve the desired output.

Edit : And never rely on implicit data type conversions.

[Updated on: Mon, 11 August 2014 01:10]

Report message to a moderator

Re: number to date format ? [message #621172 is a reply to message #621171] Mon, 11 August 2014 01:13 Go to previous messageGo to next message
tondapi
Messages: 98
Registered: August 2007
Location: usa
Member
Littlefoot thanks !, It is working fine.

Lalit Kumar B : Request the developer team to change the number type to data type.Due to some reason it is not possibale.


Re: number to date format ? [message #621187 is a reply to message #621172] Mon, 11 August 2014 07:56 Go to previous messageGo to next message
EdStevens
Messages: 295
Registered: September 2013
Senior Member
tondapi wrote on Mon, 11 August 2014 01:13
Littlefoot thanks !, It is working fine.

Lalit Kumar B : Request the developer team to change the number type to data type.Due to some reason it is not possibale.




It is possible.
But it won't be easy, since the app is already developed to a bad data model.
The limitation isn't technical, but managerial.

[Updated on: Mon, 11 August 2014 07:57]

Report message to a moderator

Re: number to date format ? [message #621188 is a reply to message #621187] Mon, 11 August 2014 08:18 Go to previous messageGo to next message
ThomasG
Messages: 3100
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
EdStevens wrote on Mon, 11 August 2014 14:56
The limitation isn't technical, but managerial.


It MIGHT be a technical reason, if one of the goals of the applications is to be as database agnostic as possible. Date types tend to be more different in their implementations across different databases than number or char types.

IF that was the reason a number type was chosen to represent a date than care has to be taken the "workarounds" doesn't negate that goal. So I wouldn't accept "some reason" from the dev team, the specific reason might be important to know for anybody who is working with the system.
Re: number to date format ? [message #621203 is a reply to message #621188] Mon, 11 August 2014 09:56 Go to previous messageGo to next message
EdStevens
Messages: 295
Registered: September 2013
Senior Member
ThomasG wrote on Mon, 11 August 2014 08:18

It MIGHT be a technical reason, if one of the goals of the applications is to be as database agnostic as possible.


Possibly, but in that case I'd counter that the app implemented 'database neutrality' in a very poor manner. Instead of writing to the 'least common denominator', the approach should be to isolate the necessary differences and abstract them behind a small set of platform-specific procedures that use a platform-neutral API.
Re: number to date format ? [message #621205 is a reply to message #621203] Mon, 11 August 2014 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

TO_CHAR and TO_DATE are database dependent functions. Wink
So in this case, the application should get the number and convert it to string.


Re: number to date format ? [message #621207 is a reply to message #621205] Mon, 11 August 2014 10:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2407
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Mon, 11 August 2014 20:40

TO_CHAR and TO_DATE are database dependent functions. Wink
So in this case, the application should get the number and convert it to string.


Completely agree Michel. And to add to you, in OP's case the data is fetched from database and not coming from application while handling the data type issue. Hence, the issue and fix remains with database. And not to forget, the performance issue that would creep sooner or later, would be fixed in database.
Re: number to date format ? [message #621212 is a reply to message #621207] Mon, 11 August 2014 12:24 Go to previous messageGo to next message
EdStevens
Messages: 295
Registered: September 2013
Senior Member
Michael/Lalit,

And how does oracle itself achieve "OS neutrality"? Isn't it by isolating OS-specific stuff to behind a procedure or even lower-level API call? Exactly as I maintain that a 'db-neutral' application should do? And as many other DBAs advocate that all access to tables should be via a package? Of course even the syntax of calling a procedure could vary between various vendors dialects of SQL.

Hmm. What does TK say about database neutrality?

"With the exception of some read-only applications, it is my contention that building a wholly database-independent application that is highly scalable is extremely hard - and is in fact quite impossible unless you know exactly how each database works in great detail." (Thomas Kyte, "Expert one-on-one Oracle", c. 2001, Wrox, pg 37).

I'd go one step further. Given his comment about "impossible unless ..." (emphasis mine), how many developers really have a detailed knowledge of how even one database product works, at the level to which Mr. Kyte refers? Much less multiple database products. And even being generous about that, given the fundamental differences in how different products handle locking and transaction control, I don't know if any level of expertise could product such an application.

Just a one-off personal observation .. I once attended a pitch for an app where the vendor claimed that their product would run on any database, but they recommended SQL Server because they "found that oracle cannot sustain more than five concurrent connections."

Of course we don't know that the OP supporting a commercial app that is trying to be database neutral. I'd actually rather hazard a guess that he's not, and his developer's use of a NUMBER to store a DATE is just one more example of the all-too-common practice of poor data typing.
Re: number to date format ? [message #621213 is a reply to message #621212] Mon, 11 August 2014 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I didn't advocate for any database neutrality implementation, my remark was just to emphasize the question is not in the scope of the database. If you declare something as a number then it is a number for the database despite what you think it is.
I always said that an application is developed for one rdbms and can't be for several ones, first for locking, transactional reasons and then for performances ones.

I always said that implementing a date as a number or a string is not only stupid it is a lack of the basic of relational theory which states that attributes have a type (which implies possible operations on it) and a range of values.

Edit: so, in short, we are in agreement. Smile

[Updated on: Mon, 11 August 2014 12:43]

Report message to a moderator

Re: number to date format ? [message #621215 is a reply to message #621213] Mon, 11 August 2014 12:54 Go to previous messageGo to next message
EdStevens
Messages: 295
Registered: September 2013
Senior Member
Michel Cadot wrote on Mon, 11 August 2014 12:42



Edit: so, in short, we are in agreement. Smile



Smile
Re: number to date format ? [message #621218 is a reply to message #621215] Mon, 11 August 2014 13:25 Go to previous message
Lalit Kumar B
Messages: 2407
Registered: May 2013
Location: World Wide on the Web
Senior Member
If I have to write a code, irrespective for an application or only for database specific operations, I wouldn't think of neutrality.

Few words from T.Kyte himself regarding date type as number:

Quote:

1) there are ONLY DISADVANTAGES.

there are no, none, nada, nunca, zero advantages.


And the reference feom asktom is here https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:77598210939534
Previous Topic: Testing for Numeric when a varchar is passed in
Next Topic: Window Functions = Analytic Functions in Oracle RDBMS?
Goto Forum:
  


Current Time: Wed Oct 01 07:46:41 CDT 2014

Total time taken to generate the page: 0.10837 seconds