| construct full date based on value yyyymm [message #632034] |
Fri, 23 January 2015 09:36  |
 |
cplusplus1
Messages: 58
Registered: October 2012
Location: usa
|
Member |
|
|
I have value YYYYMM, in column "close_per", need to make it the first date of that month and year.
Select close_per from charge_det;
want to have teh rsult as 01mmyyyy.
Can you please tell me how.
Thank you very much for the helpful info.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: construct full date based on value yyyymm [message #632045 is a reply to message #632043] |
Fri, 23 January 2015 10:28  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
What is your problem to go from YYYYMM to 01mmyyyy or dd-MM-yyyy?
I fail to see what can be the problem, it's so basic that with the almost half hundred questions you posted, almost all around strings or dates, you still unable to do this and you're still unable to correctly post a question.
I agree with BlackSwan, you are completely unskilled for this job.
Please read OraFAQ Forum Guide.
Always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
|
|
|
|
|
|
|
|
|
|
| Re: construct full date based on value yyyymm [message #632049 is a reply to message #632047] |
Fri, 23 January 2015 11:14 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
@EdStevens: It will fail, as it the input string too long. But, here are some ideas for original poster; I hope he is aware of string concatenation operation.
with x as ( select column_value c
from table( sys.odcivarchar2list( '201411', '201412', '201501', '201502', '201503' ) )
)
select c, '01' day_nr, '-' separator, substr(c,5,2) month_nr, substr(c,1,4) year_nr
from x;
Of course, it will produce garbage if the input is in different format, but at least it will not fail with any exception caused by converting it to real DATEs.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: construct full date based on value yyyymm [message #632070 is a reply to message #632050] |
Fri, 23 January 2015 16:20 |
|
cplusplus1
Messages: 58
Registered: October 2012
Location: usa
|
Member |
|
|
I just read today, France is the most hospitable country in the world. Congratulations Michel.
Using this straight:
to_char(to_date('01'||CLOSING_PER, 'ddyyyymm'),'dd-mm-yyyy')
observed too many posts by BlackSwaaan and Michel Caydot: arrogant responses , too old get some rest. There are lot of other members here who are better learn from them.
[Updated on: Fri, 23 January 2015 16:34] Report message to a moderator |
|
|
|
| Re: construct full date based on value yyyymm [message #632073 is a reply to message #632070] |
Fri, 23 January 2015 18:32 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
cplusplus1 wrote on Fri, 23 January 2015 17:20Using this straight:
Overkill. I suggest you RTFM:
The default date values are determined as follows:
•The year is the current year, as returned by SYSDATE.
•The month is the current month, as returned by SYSDATE.
•The day is 01 (the first day of the month).
•The hour, minute, and second are all 0.
Therefore:
to_char(to_date(CLOSING_PER, 'yyyymm'),'dd-mm-yyyy')
And scrap your design. Storing dates as strings sooner or later will backfire with something like 37th month of year ABCD.
SY.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
