topic Re: Getting an error while using Cast function in Operating System - OpenVMS

Getting an error while using Cast function

waliamanish — Tue, 09 Nov 2004 07:10:10 GMT

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

Re: Getting an error while using Cast function

Ian Miller. — Tue, 09 Nov 2004 07:18:20 GMT

try adding a colon after the year
31-AUG-2003:00:00:00.00

Re: Getting an error while using Cast function

waliamanish — Tue, 09 Nov 2004 07:23:33 GMT

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;

Re: Getting an error while using Cast function

waliamanish — Tue, 09 Nov 2004 07:25:18 GMT

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

Re: Getting an error while using Cast function

Wim Van den Wyngaert — Tue, 09 Nov 2004 07:53:33 GMT

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

Wim

Re: Getting an error while using Cast function

waliamanish — Tue, 09 Nov 2004 08:11:38 GMT

I have used this function for both date as well as Date + time....
It gives error in both cases.

Re: Getting an error while using Cast function

Wim Van den Wyngaert — Tue, 09 Nov 2004 08:13:57 GMT

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

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

Wim

Re: Getting an error while using Cast function

Kris Clippeleyr — Tue, 09 Nov 2004 08:17:33 GMT

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

Re: Getting an error while using Cast function

waliamanish — Tue, 09 Nov 2004 08:20:27 GMT

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.

Re: Getting an error while using Cast function

waliamanish — Tue, 09 Nov 2004 08:23:53 GMT

Kris, U have already declared P_date as date type...
My objective is to convert String to date.

Re: Getting an error while using Cast function

Wim Van den Wyngaert — Tue, 09 Nov 2004 08:40:51 GMT

I'm no RDB specialist. Try google.

Wim

Re: Getting an error while using Cast function

Kris Clippeleyr — Tue, 09 Nov 2004 08:56:30 GMT

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

Re: Getting an error while using Cast function

waliamanish — Tue, 09 Nov 2004 22:09:00 GMT

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