To all

I have this expression in my select:

Cast(CAST(CAST("TimePlay Last Used Date" AS CHAR(10))+' '+ CAST("Timeplay Last Used Time" AS CHAR(8))AS TIMESTAMP)+ (interval TimeToBeUsed minute) as CHAR(30))

The first part combines a date and time field into a timestamp the second part attempts to add TimeToBeUsed (another field in the table) minutes the the result timestamp. This does not work in ElevateDB because it expects a constant after interval . These are my questions:
1. What is the best way to combine date and time fields into a timestamp?
2. How can I add the value of another field (in minutes) to a timestamp?

Thanks,

Michael

Michael,

> The first part combines a date and time field into a timestamp the second part attempts to add TimeToBeUsed (another field in the table) minutes the the result timestamp. This does not work in ElevateDB because it expects a constant after interval . These are my questions:
> 1. What is the best way to combine date and time fields into a timestamp?
> 2. How can I add the value of another field (in minutes) to a timestamp?

In this case I would write a function. Here some code to test:

SCRIPT
BEGIN

   DECLARE LastUsedDate Date DEFAULT '2014-7-30';
   DECLARE LastUsedTime Time DEFAULT '4:30';
   DECLARE TimeToBeUsed INTERVAL MINUTE DEFAULT '30';

   DECLARE Temp VARCHAR;
   DECLARE ResultTimeStamp TIMESTAMP;

   SET Temp = CAST(LastUsedDate as VARCHAR) + ' ' + cast(LastUsedTime
as VARCHAR);
   SET ResultTimeStamp = CAST(Temp as Timestamp) + TimeToBeUsed;

END

Uli

Michael


I think you may be over complicating things (well I always think that when I see more than one CAST). Can you post the column types so that I can have a play around here.

Roy Lambert

Michael


If I'd bothered to read your post properly the first time round I would have seen the column types. After a bit of playing try

CAST(CAST(CAST("TimePlay Last Used Date" AS VARCHAR(10))+' '+CAST("Timeplay Last Used Time" AS VARCHAR(5)) AS TIMESTAMP) + CAST(TimeToBeUsed minute AS INTERVAL  MINUTE) AS VARCHAR(30))


Roy Lambert