Home » SQL & PL/SQL » SQL & PL/SQL » month values from date/varchar2 column (merged)
month values from date/varchar2 column (merged) [message #336862] Tue, 29 July 2008 04:27 Go to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi,
i had a column with datatype varchar2 and it consists of dates in it i want to get date from that column. i used to_date but im getting error

thanks in advance,
radha
Re: to get date value from varchar2 column [message #336866 is a reply to message #336862] Tue, 29 July 2008 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix your expression and then you will no have an error.

Regards
Michel
Re: to get date value from varchar2 column [message #336882 is a reply to message #336866] Tue, 29 July 2008 05:08 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
True; although, there's a high possibility that data is invalid itself. Who knows which "date format" users chose to store "date" values? There may be (as today is 23.07.2008 (dd.mm.yyyy)):
- 23.07.2008.
- 07-23-08
- 07.23.2008
- jul-07-08
etc. etc.

Therefore, fixing expression might not be enough and date extraction not that easy.

However, there's one good thing in the whole story: it will teach you that dates should be stored in DATE column, not character one.
Re: to get date value from varchar2 column [message #336889 is a reply to message #336882] Tue, 29 July 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"expression" should be "expressions", I meant all function parameter values. Wink

Regards
Michel
Re: to get date value from varchar2 column [message #336898 is a reply to message #336862] Tue, 29 July 2008 05:29 Go to previous messageGo to next message
raghu2110
Messages: 5
Registered: July 2008
Location: INDIA
Junior Member
try using to_char() expn

Regards
P.Raghuveer
Re: to get date value from varchar2 column [message #336902 is a reply to message #336898] Tue, 29 July 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think that to_char on a "column with datatype varchar2" will help in anything.

Regards
Michel
Re: to get date value from varchar2 column [message #337167 is a reply to message #336902] Wed, 30 July 2008 01:36 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi,
i tried using to_date(columnname,expresion)
but im getting error non numeric....
hope help me.

thanks in advance,
radha
Re: to get date value from varchar2 column [message #337187 is a reply to message #337167] Wed, 30 July 2008 02:11 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As Michel said,
Michel
I don't think that to_char on a "column with datatype varchar2" will help in anything


Also, check my first reply to your question - there are probably too many different "formats" used in this column, and you'll probably have to write a PL/SQL procedure which will take care about all the possibilities, using bunch of different date formats as well as bunch of exception handlers.
Re: to get date value from varchar2 column [message #337191 is a reply to message #337187] Wed, 30 July 2008 02:26 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi,
i tried all date formats once im getting error not valid month, once im getting non numeric was found where numeric expected.
please help me by giving example.
in my columns the dates are in DD-MON-RR format.

thanks in advance,
radha
Re: to get date value from varchar2 column [message #337198 is a reply to message #337191] Wed, 30 July 2008 02:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Example that is possible with the information you gave us:

... to_date(column,'DD-MON-RR') ...


Quote:

in my columns the dates are in DD-MON-RR format.



Maybe in most of your columns.

But since you get different errors I suspect that there are other or even an invalid formats used in some of the rows in some of the columns.

NEVER STORE DATES AS STRINGS

Re: to get date value from varchar2 column [message #337201 is a reply to message #337198] Wed, 30 July 2008 02:36 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi,
i had wriiten in the same way as u have send. but my column have other values like chars,numbers also not only date values so please help me.

thanks in advance,
radha
Re: to get date value from varchar2 column [message #337208 is a reply to message #337201] Wed, 30 July 2008 02:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Store dates in date columns. period.

Every other possible workaround would depend on the specific situation and the specific data, and you haven't posted either.
Re: to get date value from varchar2 column [message #337210 is a reply to message #337201] Wed, 30 July 2008 02:56 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
radhavijay wrote on Wed, 30 July 2008 09:36
but my column have other values like chars,numbers also not only date values
And which date(s) shall these values be converted to?
You may find one idea in this link on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:890580198758.
But, as storing dates in VARCHAR2 column is not wise at all, you shall not do it.

[Edit: Corrected typos]

[Updated on: Wed, 30 July 2008 02:58]

Report message to a moderator

Re: to get date value from varchar2 column [message #337212 is a reply to message #337210] Wed, 30 July 2008 03:05 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
HI,
I DIDNT STORE MY CLIENT GAVE ME IN THAT WAY
Re: to get date value from varchar2 column [message #337217 is a reply to message #337212] Wed, 30 July 2008 03:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You do of course realize, that if you had instead of posting :

Quote:

i had a column with datatype varchar2 and it consists of dates in it i want to get date from that column. i used to_date but im getting error.



( Which basically only says "I did something weird an something weird happened" )

Posted
Quote:

I have been given data by a client that consists of varchar2 columns which sometimes contain dates and sometimes not.

Is there a way to extract the date values that contain a valid date and set the others to a default date?



That is, a SOMEWHAT complete description of the problem, the question would probable have been resolved hours ago?

You can try this save_to_date function, which basically works like to_date, only returns a default date when the conversion is unsuccessful :
CREATE OR REPLACE FUNCTION save_to_date(IN_CHAR IN VARCHAR, IN_FORMAT IN VARCHAR ) RETURN date AS

  v_temp_date date;

BEGIN

  v_temp_date := To_date(IN_CHAR,IN_FORMAT);

  RETURN v_temp_date;

EXCEPTION
  WHEN OTHERS THEN
    RETURN To_Date('01.01.1980','dd.mm.yyyy');


END;
/



Re: to get date value from varchar2 column [message #337218 is a reply to message #337212] Wed, 30 July 2008 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DON'T YELL.
We are not responsible of what you have.

You have to know all the possible formats and then create a procedure to try to convert the string into a date using all these possible formats.
Of course, you have to take of possible ambiguity.

Regards
Michel
Re: to get date value from varchar2 column [message #337241 is a reply to message #337218] Wed, 30 July 2008 04:58 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi
thanx alot.
i will try using tht fn.

thanks,
radha
Re: to get date value from varchar2 column [message #337255 is a reply to message #337241] Wed, 30 July 2008 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use IM speak.

Regards
Michel
Re: to get date value from varchar2 column [message #337265 is a reply to message #337167] Wed, 30 July 2008 06:05 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
radhavijay wrote on Wed, 30 July 2008 07:36
hi,
i tried using to_date(columnname,expresion)
but im getting error non numeric....
hope help me.

thanks in advance,
radha

try using
to_date(columnname,correctexpresion)
Re: to get date value from varchar2 column [message #337426 is a reply to message #337265] Wed, 30 July 2008 19:10 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi,
actually my problem is my client used dff so when we enter data from front end tht data is storing in attribute columns in backend.nw i want to build a report by using the dates as parameters. i result must be in between the dates i hv mentioned. for giving runtime parameters i must take values from attribute column. tht column is varchar.
i tried

to_date(nvl(columnname,'01-JAN-2000'),'DD-MON-YY')between :p_from_date and :p_to_date
i got error non numeric is found where numeric expeted and if i change format i got invalid month

i used trim then i didnt get error but i got wrong data
nVL(TRIM(columnname),'01-JAN-51') BETWEEN TRIM(:P_FROM_DATE) AND TRIM(:P_TO_DATE)

so please help me
im new to this oracle apps also.

thanks in advance,
radha
Re: to get date value from varchar2 column [message #337432 is a reply to message #336862] Wed, 30 July 2008 21:24 Go to previous messageGo to next message
BlackSwan
Messages: 25044
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above


Read Concepts Manual to learn the difference between STRINGS & DATES.

use TO_DATE() & TO_CHAR() to convert between the 2 datatypes.
Re: to get date value from varchar2 column [message #337438 is a reply to message #336862] Wed, 30 July 2008 21:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Let us step back a moment. If you want an answer that helps you need to do two things for us:

Quote:
1) you say "I GOT AN ERROR" but hey... I do not believe it. I see no error number/message. If you had an error then why have you not posted the error number and message. Everyone is assuming they know what error you are getting but I believe in the idea that I am right and everyone else is wrong can in fact be true. Think slavery in centuries ago.

2) you need to post some sql code that runs and generates the error. Then we can all see what you are doing.

If you do these two things you will get a better quality response from everyone here.

Good luck, Kevin
Re: to get date value from varchar2 column [message #337441 is a reply to message #337438] Wed, 30 July 2008 22:14 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi,
code is:

SELECT s.attribute3
FROM table1 s,table2 r
WHERE s.column2=r.column2(+) AND
s.attribute3 BETWEEN :P_FROM_DATE AND :P_TO_DATE

column3 is vachar column
and tht is dff when we enter data from frontend the data is storing in attribute columns at backend.

nw i need toget is when i give the date ranges tht are in attribute column then i must get the output dates tht are in between the dates i given.


ATTRIBUTE3 is VARCHAR column

i used to_date(columnname,expression) then i got error
non numberic was found where numeric expected and if i change date formats i got invalid month.

i used trim then i didnt get error but i got wrong data
nVL(TRIM(columnname),'01-JAN-51') BETWEEN TRIM(:P_FROM_DATE) AND TRIM(:P_TO_DATE)

please help me
its urgent.
i need to submit the reports today.
hope u help me and hope tht i wrote my issue clearly.

thanks in advance,
radha
Re: to get date value from varchar2 column [message #337442 is a reply to message #336862] Wed, 30 July 2008 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 25044
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

Since you can not or will not follow Posting Guidelines, You're On Your Own (YOYO)!

[Updated on: Wed, 30 July 2008 22:20] by Moderator

Report message to a moderator

Re: to get date value from varchar2 column [message #337446 is a reply to message #336862] Wed, 30 July 2008 22:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK

first, there is something wrong with your keyboard. Slow down and don't use abbreviations, it only annoys people.

next, I still dont' see any error number/message. I am looking for ORA-?????. WHAT IS IT.

now, let me ask you some questions.

1) do you know how to convert a character string to a date?

to_date('01-jan-2001','dd-mon-rrrr')

is an example. this is composed of two pieces, a date string, and a format string.

2) do you know what will happen if you do this:

select to_date('01-jan-2001','yyyymmdd') from dual;

If so tell me why.

You see, there are several different date related errors. Most of them are a result of the format mask not matching the string being converted.

3) have you examined the data you are trying to convert? try this and then post the results for us.

SELECT s.attribute3 FROM table1 s where rownum < 20;

4) where do you get your format string for the date string you are converting?

Quote:
Is it passed to you?
Is it in your table along side the string column holding the date?
Do assume all dates in the column have the same format?

Answer these questions and get back to us. Understand too that is is midnight here in the states so I will be heading for bed soon.

Good luck, Kevin
Re: to get date value from varchar2 column [message #337451 is a reply to message #337446] Wed, 30 July 2008 22:55 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
error numbers are:
ora-01858- non numeric was found where numeric expected.
ora-01843- not a valid month

in my table the attribute columns have values in this way:
attribute2
abcd
21-JUN-08
NULL

12
01-JAN-07
06-DEC-09

NOW IM TRYING IN
SELECT X.ATTRIBUTE FROM X,Y WHERE X.COLUMN=Y.COLUMN AND TO_DATE(X.ATTRIBUTE,'DD-MON-YY') BETWEEN :P_FROM_DATE AND :P_TO_DATE

WHEN I RUN THIS QUERY THN IM GETTING ERRORS
ORA-01858 AND ORA-01843

WHEN I USED TRIM
TO_DATE(nVL(TRIM(columnname),'01-JAN-51'),'DD-MON-YY') BETWEEN TRIM(:P_FROM_DATE) AND TRIM(:P_TO_DATE)
I GOT OUTPUT WITH NO ERRORS BUT

FOR EXAMPLE IF I ENTER DATA IN BELOW WAY
P_FROM_DATE=01-JAN-08
P_TO_DATE=01-SEP-08

THEN IM GETTING OUTPUT IN BELOW WAY
ATTRINUTE
21-JUN-08
01-JAN-07
06-DEC-09

THE O/P IS BEYOND THE RANGE.
I MUST GET ONLY VALUE 21-JUN-08. BUT IM GETTING ALL VALUES.

IM NOT GETTING HW TO RECTIFY IT.
please ANYONE HELP ME.


THANKS IN ADVANCE,
RADHA




Re: to get date value from varchar2 column [message #337453 is a reply to message #337451] Wed, 30 July 2008 23:31 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi
without todate also im getting correct o/p in toad
but if i run in report builder im getting error.

01847-day of month must be between first daya and last day of the month.

SELECT DISTINCT r.ATTRIBUTE2
FROM table1 s,
table2 r
WHERE s.column1=s.column2(+) AND
r.ATTRIBUTE2 BETWEEN :P_FROM_DATE AND :P_TO_DATE
this is the code i run in toad i got correct o/p.

if the same thing i run in reportbuilder i got error

01847-day of month must be between first daya and last day of the month.
Re: to get date value from varchar2 column [message #337454 is a reply to message #336862] Wed, 30 July 2008 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 25044
Registered: January 2009
Location: SoCal
Senior Member
What is the difference between date & string datatypes?
Which do you have & how do you convert 1 to the other?
Re: to get date value from varchar2 column [message #337469 is a reply to message #337451] Thu, 31 July 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 30 July 2008 10:20
DON'T YELL.
We are not responsible of what you have.

You have to know all the possible formats and then create a procedure to try to convert the string into a date using all these possible formats.
Of course, you have to take of possible ambiguity.

Regards
Michel


Re: to get date value from varchar2 column [message #337631 is a reply to message #337453] Thu, 31 July 2008 08:10 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
You just don't understand anything anyone is telling you or are too much of a stubborn ass to implement it. Is this how you try to solve other life problems? Everyone is getting a little tired of repeating the same things over and over and have you ignore them. You will soon find no one giving you any responses. You show no signs of progress. You have hit your maximum level of learning ability. A bit harsh, but seem to be the reality.
Re: to get date value from varchar2 column [message #337670 is a reply to message #336862] Thu, 31 July 2008 10:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, so looking at your data

Quote:
in my table the attribute columns have values in this way:
attribute2
abcd
21-JUN-08
NULL

12
01-JAN-07
06-DEC-09


Your column has lots of stuff in it. This is a generic column wherein some of the entries may have valid date values. Your query it trying to convert all entries in this column to a date which won't work. You must only convert those entries that have actual dates in them. So let me ask you this:

when your code is looking at some particular row in this table, how does it know if the column is supposed to contain a date, or something else?

If your code does know that the column contains a date, how does it know that the value is actually a valid date?

here is a simple fix for your problem (time to put this post to bed I think). This function will take a string and date format and attempt to convert the string to a date using your format. If the conversion fails, you will not get an error, but instead the function will return null. If you do not supply a date format you will use a default which seems to fit your dates.

THIS IS A HACK (but then again so is the table design you are forced to work with), AND NOT A GOOD SOLUTION FOR OTHERS TO USE. But it will get you around your problem of the errors.

create or replace
function convert_to_date_or_null (string_p in varchar2,date_format_p in varchar2 := 'dd-mon-rrrr') return date is
   date_v date;
begin
   begin
      date_v := to_date(string_p,date_format_p);
   exception when others then null;
   end;
   return (date_v);
end;
/
show errors


You can now do this:

SELECT s.attribute3
FROM table1 s,table2 r
WHERE s.column2=r.column2(+) AND
convert_to_date_or_null(s.attribute3) BETWEEN :P_FROM_DATE AND :P_TO_DATE

This procedure is by the way, almost the same thing that ThomasG already gave you earlier in the posting stream. If you do not understand how this procedure works, then I am afraid there is little any of us can do for you because that would mean you do not have the minimum skills necessary to work in the Oracle space.

Good luck, Kevin
Re: to get date value from varchar2 column [message #337686 is a reply to message #337670] Thu, 31 July 2008 11:49 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Kevin Meade wrote on Thu, 31 July 2008 11:16


SELECT s.attribute3
FROM table1 s,table2 r
WHERE s.column2=r.column2(+) AND
convert_to_date_or_null(s.attribute3) BETWEEN :P_FROM_DATE AND :P_TO_DATE



Kevin, I know your focus was on the shleprocked date column in this awful design, but there is still the original flaw that a DATE is being compared to 2 strings, unless this is code from an Oracle Form, but that was never stated by the OP (not surprisingly).
to get month value from date value column [message #338130 is a reply to message #336862] Sun, 03 August 2008 22:20 Go to previous messageGo to next message
radhavijay
Messages: 29
Registered: July 2008
Junior Member
hi all,
i had a small doubt.
i had a column with datatype as date.the format is 4/1/2007.
now i need month and year values from that date.
but month and year are runtime parameters.
i.e when i give month=july and year= 2008 then this parameters must match the values in the column and give me output.
my doubt is how to get month and year values and change month value 4 to april.using string im getting month value but how to convert it to april.plz help me.

thanks in advance,
radhavijay
Re: to get month value from date value column [message #338131 is a reply to message #338130] Sun, 03 August 2008 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 25044
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

>i had a column with datatype as date.the format is 4/1/2007.
No you do NOT! Date datatype have NO format!

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm

[Updated on: Sun, 03 August 2008 22:27] by Moderator

Report message to a moderator

Re: to get month value from date value column [message #338133 is a reply to message #338130] Sun, 03 August 2008 22:42 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> my doubt is how to get month and year values and change month value 4 to april.

If the column has DATE datatype, you may convert it to "april" as well. Just use correct format mask in TO_CHAR function (of course NLS_DATE_LANGUAGE shall be set to ENGLISH).

But this is not recommended way, as Oracle cannot use index on that DATE column. Much more better is to construct border dates and use comparison, like this:
WHERE <date_column> >= TO_DATE( '2008-april', 'YYYY-fmmonth' )
  AND <date_column> < ADD_MONTHS( TO_DATE( '2008-april', 'YYYY-fmmonth' ), 1 )
Previous Topic: Concurrent Insert statements Using Dbms_job.Submit
Next Topic: ORA-01427
Goto Forum:
  


Current Time: Wed Dec 07 08:44:18 CST 2016

Total time taken to generate the page: 0.11340 seconds