Operating System - OpenVMS
cancel
Showing results for 
Search instead for 
Did you mean: 

Getting an error while using Cast function

 
SOLVED
Go to solution
waliamanish
Occasional Advisor

Getting an error while using Cast function

declare :p_char char(23);

declare :p_date date vms;

begin

set :p_char = '31-AUG-2003 00:00:00.00';

set :p_date = cast(:p_char as date vms);

cont> end;

%RDB-E-CONVERT_ERROR, invalid or unsupported data conversion

-COSI-F-IVTIME, invalid date or time
12 REPLIES
Ian Miller.
Honored Contributor

Re: Getting an error while using Cast function

try adding a colon after the year
31-AUG-2003:00:00:00.00
____________________
Purely Personal Opinion
waliamanish
Occasional Advisor

Re: Getting an error while using Cast function

I tried adding COLON after Year part ....
It stiil gives the same error.
I am not sure that the way i am using CAST function is correct.
See the error below(after putting COLON):

declare :p_char char(23);

declare :p_date date vms;

begin

set :p_char = '31-AUG-2003:00:00:00.00';

set :p_date = cast(:p_char as date vms);

end;
waliamanish
Occasional Advisor

Re: Getting an error while using Cast function

I forgot to add Error message:

SQL> declare :p_char char(23);
SQL>
SQL> declare :p_date date vms;
SQL>
SQL> begin
cont>
cont> set :p_char = '31-AUG-2003:00:00:00.00';
cont>
cont> set :p_date = cast(:p_char as date vms);
cont>
cont> end;
%RDB-E-CONVERT_ERROR, invalid or unsupported data conversion
-COSI-F-IVTIME, invalid date or time
Wim Van den Wyngaert
Honored Contributor

Re: Getting an error while using Cast function

Check your RDB doc : is the function for date + time or only date ?

Wim
Wim
waliamanish
Occasional Advisor

Re: Getting an error while using Cast function

I have used this function for both date as well as Date + time....
It gives error in both cases.
Wim Van den Wyngaert
Honored Contributor

Re: Getting an error while using Cast function

http://gsbwww.uchicago.edu/computing/research/SASManual/accdb/z0459965.htm

Its not preciser than seconds. Remove ".00".

Wim
Wim
Kris Clippeleyr
Honored Contributor

Re: Getting an error while using Cast function

I'm no SQL expert, but IMHO you don't need the "cast".

SQL> declare :p_date date vms;
SQL> begin
cont> set :p_date='31-AUG-2003';
cont> end;
SQL> print :p_date;
P_DATE
31-AUG-2003 00:00:00.00
SQL>

Greetz,
Kris
I'm gonna hit the highway like a battering ram on a silver-black phantom bike...
waliamanish
Occasional Advisor

Re: Getting an error while using Cast function

remove .00 also gives the same error. Can u tell me if i can use Cast function the way i am doing it right now. IF Not then tell me how can casting be achieved.
waliamanish
Occasional Advisor

Re: Getting an error while using Cast function

Kris, U have already declared P_date as date type...
My objective is to convert String to date.
Wim Van den Wyngaert
Honored Contributor

Re: Getting an error while using Cast function

I'm no RDB specialist. Try google.

Wim
Wim
Kris Clippeleyr
Honored Contributor
Solution

Re: Getting an error while using Cast function

As I said, I'm no SQL expert, but this sems to work:

SQL> declare :p_char char(23);
SQL> declare :p_date date vms;
SQL> begin
cont> set :p_char='2003083123222120';
cont> set :p_date=cast(:p_char as date vms);
cont> end;
SQL> print :p_char, :p_date;
P_CHAR P_DATE
2003083123222120 31-AUG-2003 23:22:21.20
SQL>

Greetz,
Kris
I'm gonna hit the highway like a battering ram on a silver-black phantom bike...
waliamanish
Occasional Advisor

Re: Getting an error while using Cast function

Kris,
Your solution worked.
Thanks!!! I was supplying string in improper format..
Thanks to everyone.