Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Problem using INTERVAL.. |
Thu, Jul 29 2021 1:07 AM | Permanent Link |
Ian Branch | Hi Team,
I have the following sql in edbmgr.. {sql} select * from jobtickets where (current_timestamp - lastmodified) > INTERVAL '1' HOUR order by buscode, jobno {sql} When I click 'Prepare' I get the following error.. >> ElevateDB Error #700 An error was found in the statement at line 2 and column 44 (Expected Interval Day To MSecond expression but instead found INTERVAL '1' HOUR)<< lastmodified is a timestamp field. Am I misunderstanding what is required?? Regards & TIA, Ian |
Thu, Jul 29 2021 7:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
If you don't use them frequently INTERVALs are the spawn of the anti-god of your choice (hoping that doesn't offend anyone) Your problem comes from fact that you're trying to compare a time with a timestamp so you need to convert. Try (CURRENT_TIMESTAMP - LastModified) HOUR > INTERVAL '1' HOUR, Roy Lambert ps if it makes you feel any better its just taken me 15 inutes to figure out. |
Thu, Jul 29 2021 4:38 PM | Permanent Link |
Ian Branch | Ah Ha! Thanks Roy.
That little Caveat is missing in the help. Regards, Ian |
Wed, Aug 4 2021 7:20 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ian,
<< That little Caveat is missing in the help. >> It's covered here: https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Arithmetic_Operators under "Date, Time, and Timestamp Subtraction". Tim Young Elevate Software www.elevatesoft.com |
Thu, Aug 5 2021 4:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
Just wondering, would it be possible to have it as a CAST eg CAST ((CURRENT_TIMESTAMP - LastModified) AS HOUR) > INTERVAL '1' HOUR, doeen't gain or loose anything but might be easier for people to understand. Roy Lambert |
Thu, Aug 5 2021 7:26 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Just wondering, would it be possible to have it as a CAST eg >> You can, but you would have to use the full interval type name: CAST ((CURRENT_TIMESTAMP - LastModified) AS INTERVAL HOUR) > INTERVAL '1' HOUR, Tim Young Elevate Software www.elevatesoft.com |
Thu, Aug 5 2021 7:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
If its a trivial addition I think it would be worthwhile for those who use INTERVAL at extended intervals Roy Lambert |
Mon, Aug 9 2021 7:22 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< If its a trivial addition I think it would be worthwhile for those who use INTERVAL at extended intervals >> I'm not sure I understand what you're saying - it's *already* possible to do what you indicated. Tim Young Elevate Software www.elevatesoft.com |
Sat, Aug 14 2021 6:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
My misunderstanding. I thought you were saying that this is what the syntax would have to be not that it was already there Roy Lambert |
Tue, Aug 17 2021 6:58 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< My misunderstanding. I thought you were saying that this is what the syntax would have to be not that it was already there >> No problem. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |