| Duchi |
Posted: May 20, 2008 10:51:03 AM |
Total Post: 12
Joined: Jan, 2008
|
Can anyone tell me what is wrong with the sql below?
I'm getting a datatype conversion error
:variable is of integer or number datatype
select add_months(trunc(:variable),'YYYY'), '6') from dual
any help would be greatly appreciated |
|
|
 Mohammed Taj |
| Posted: May 20, 2008 12:25:38 PM |  |
|
Total Post: 746
Joined: Jul, 2007
|
Yes,
Becuase you trying to add date (6) month in number datatype.
anyway your query is also wrong.
what is your expected requirement.
|
|
|
|
|
| fozturk |
| Posted: May 20, 2008 01:47:06 PM | |
|
Total Post: 69
Joined: Sep, 2007
|
I guess you want to build this query;
select add_months(
to_date(trunc(:variable),'YYYY'), '6') from dual
i hope it helps
|
|
|
|
|
| Duchi |
| Posted: May 20, 2008 01:48:15 PM | |
|
Total Post: 12
Joined: Jan, 2008
|
I'm required to pass a year as a bind variable and the output should be 07/01/2008
Is that possible?
|
|
|
|
|
| fozturk |
| Posted: May 20, 2008 02:04:40 PM | |
|
Total Post: 69
Joined: Sep, 2007
|
ok try this;
select add_months(to_date('01/01/'||to_char(:number),'dd/mm/yyyy'),6) from dual
does it work?
|
|
|
|
|
| Duchi |
| Posted: May 20, 2008 02:08:45 PM | |
|
Total Post: 12
Joined: Jan, 2008
|
I came up with an alternate solution trying to concatenate the bind variable with the string and it sure works, my concern was that I'm not missing any function that oracle provided which would accept a year as a parameter and can return a date
thanks for all the help
if you know any function please update this thread
thanks,
|
|
|
|
|
| fozturk |
| Posted: May 20, 2008 02:19:41 PM | |
|
Total Post: 69
Joined: Sep, 2007
|
did you try my previous post:
select add_months(to_date(trunc(:variable),'YYYY'), '6') from dual
it shall work
|
|
|
|
|
| Duchi |
| Posted: May 20, 2008 02:24:10 PM | |
|
Total Post: 12
Joined: Jan, 2008
|
it worked sorry ! but what i was trying to say was i knew about the solution i wanted to see if there was a function in oracle that did the same functionality without having to concatenate
|
|
|
|
|
| Duchi |
| Posted: May 20, 2008 02:27:15 PM | |
|
Total Post: 12
Joined: Jan, 2008
|
your previous post did not give me what i needed
the result from your previous post trunc'd the date and added 6 months to it i needed it to trunc the year which will give me the 1st day of the year Jan'01 and add 6 months which would yield Jul '01 which was what i needed
|
|
|
|
|
| fozturk |
| Posted: May 20, 2008 02:31:58 PM | |
|
Total Post: 69
Joined: Sep, 2007
|
yes i tried it now and saw that :) i checked the functions of oracle and there is not a first day of the yeaar, i guess you must give the month parameter also to find the first day, you can use the first_day function.
eq: first_day(to_date('012008','mmyyyy'))
|
|
|
|
|
| Duchi |
| Posted: May 20, 2008 03:12:59 PM | |
|
Total Post: 12
Joined: Jan, 2008
|
thank you. I appreciate your assistance. sometimes, I hit a brick wall trying to avoid concatenationg date strings but inevitably, have to continue doing it
thanks to everyone for help
|
|
|
|
|
| fozturk |
| Posted: May 20, 2008 03:37:55 PM | |
|
Total Post: 69
Joined: Sep, 2007
|
never midn, we will continue to heelp eachother:) i m trying to have my OCP certificate and this foroum helps melol take care
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
