At 07:31 AM 8/08/2008, you wrote:

>On Thursday 07 August 2008 5:22:51 pm Alan McDonald wrote:
><SNIP>
>> > > Error Message:
>> > > ----------------------------------------
>> > > Overflow occurred during data type conversion.
>> > > conversion error from string "CURRENT_TIMESTAMP".
>>
>> use single quotes
>> Alan

NOT !

I think people sometimes confuse context variables with date literals. They are different organisms. Date literals must be single-quoted, context variables must not.

At 07:31 AM 8/08/2008, Dee Holtsclaw wrote:

>My initial guess is that CURRENT_TIMESTAMP is being interpreted as a string
>when it should not be.

Quoting CURRENT_TIMESTAMP would cause that.

>Maybe a CAST('CURRENT_TIMESTAMP' AS TIMESTAMP) is in
>order ...

That would certainly *cause* this problem. ;-) If you put quotes around a context variable, it's not a context variable any more, just a string consisting of the characters 'C|U|R|R|E|N|T|_|T|I|M|E|S|T|A|M|P'. There is nothing in that string that could convert to a timestamp type - hence the exception.

You never have to cast context variables to their native data type. You can, of course, cast them to other types. For example:

...
declare variable MyTimestampString varchar(24);
...
begin
MyTimestampString = cast(current_timestamp as varchar(24))
...
..
end

And this cast expression is valid, too:

cast(current_timestamp as date)

./heLen