Getting an error while using Cast function

[waliamanish]

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

[Ian Miller.]

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

[waliamanish]

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]

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]

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

Wim

[waliamanish]

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

[Wim Van den Wyngaert]

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

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

Wim

[Kris Clippeleyr]

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

[waliamanish]

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]

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

[Wim Van den Wyngaert]

I'm no RDB specialist. Try google.

Wim

[Kris Clippeleyr · Accepted solution]

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

[waliamanish]

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