Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Variable Interval |
Wed, Jul 30 2014 2:31 PM | Permanent Link |
Michael | 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 |
Thu, Jul 31 2014 3:16 AM | Permanent Link |
Uli Becker | 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 |
Thu, Jul 31 2014 3:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Jul 31 2014 9:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |