OraFAQ Forum: SQL & PL/SQL » number to date format ?

Home » SQL & PL/SQL » SQL & PL/SQL » number to date format ?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

number to date format ? [message #621169]

Mon, 11 August 2014 01:03

tondapi
Messages: 99
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,

Report message to a moderator

Re: number to date format ? [message #621170 is a reply to message #621169]

Mon, 11 August 2014 01:07

Littlefoot
Messages: 21808
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>

Report message to a moderator

Re: number to date format ? [message #621171 is a reply to message #621169]

Mon, 11 August 2014 01:08

Lalit Kumar B
Messages: 3174
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

tondapi
Messages: 99
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.

Report message to a moderator

Re: number to date format ? [message #621187 is a reply to message #621172]

Mon, 11 August 2014 07:56

EdStevens
Messages: 1376
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

ThomasG
Messages: 3211
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.

Report message to a moderator

Re: number to date format ? [message #621203 is a reply to message #621188]

Mon, 11 August 2014 09:56

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: number to date format ? [message #621205 is a reply to message #621203]

Mon, 11 August 2014 10:10

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: number to date format ? [message #621207 is a reply to message #621205]

Mon, 11 August 2014 10:20

Lalit Kumar B
Messages: 3174
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.
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.

Report message to a moderator

Re: number to date format ? [message #621212 is a reply to message #621207]

Mon, 11 August 2014 12:24

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: number to date format ? [message #621213 is a reply to message #621212]

Mon, 11 August 2014 12:42

Michel Cadot
Messages: 68645
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

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Michel Cadot wrote on Mon, 11 August 2014 12:42

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

Report message to a moderator

Re: number to date format ? [message #621218 is a reply to message #621215]

Mon, 11 August 2014 13:25

Lalit Kumar B
Messages: 3174
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

Report message to a moderator

Previous Topic:	Testing for Numeric when a varchar is passed in
Next Topic:	Window Functions = Analytic Functions in Oracle RDBMS?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 03:17:05 CDT 2024