Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
How to add Day_Num to Reg_Date? |
Thu, Aug 2 2012 9:55 PM | Permanent Link |
Barry | My table has 2 columns:
Reg_Date date, and Day_Num integer. I need to add Day_Num to Reg_Date and get a date. I know I can use: select reg_date + interval '5' day from table1 but how do I replace '5' with Day_Num? The Interval expects a constant and the following won't work: select reg_date + interval day_num day from table1 TIA Barry |
Fri, Aug 3 2012 4:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
select reg_date + CAST(day_num AS INTERVAL day) from table1 Roy Lambert [Team Elevate] |
Fri, Aug 3 2012 5:26 PM | Permanent Link |
Barry | Roy,
Thanks, that worked. 1) What confuses me is this syntax works: select date '2012-01-01' + interval '5' day from table1 2) but replacing the '5' with an integer column name, won't work: select date '2012-01-01' + interval day_num day from table1 3) As you pointed out, I have to change the syntax of the interval phrase to: select date '2012-01-01' + cast(day_num as interval day) from table1 So unless I am adding a string constant, I will have to use Cast(). There is nothing wrong with that, but why does #1 work? I would have thought the DBMS would have known day_num was an integer and substituted that in for the constant '5'. Two different syntaxes for date arithmetic is a puzzle that I am trying to wrap my head around. BTW, I looked through the ElevateDb pdf manuals/help before posting yesterday, and couldn't find any interval arithmetic that used column names. They only used constants. So I would have been stuck here for a while if you hadn't come along. Barry v2.09 |
Fri, Aug 3 2012 6:01 PM | Permanent Link |
Fernando Dias Team Elevate | Barry,
This syntax is SQL standard syntax, not some weird EDB proprietary syntax. INTERVAL '5' DAY is the standard syntax to represent a literal of type INTERVAL DAY, and CAST is also the standard SQL function to convert types. What happens here is your column "day_num" is not of type INTERVAL DAY but of type INTEGER, and that's why you have to convert the integer column to the correct type using CAST( ) before adding it to a date. If "day_num" was of type INTERVAL DAY you wouldn't have to use CAST to convert types , you would be allowed to write: SELECT DATE '2012-01-01' + day_num FROM table1 So, in fact there aren't 2 different syntax rules, what happens is just the need to convert one type to another using the CAST function, thats all. -- Fernando Dias [Team Elevate] |
Fri, Aug 3 2012 6:16 PM | Permanent Link |
Barry | Fernando,
Thanks for the explanation. I didn't realize I could create a column type of "Interval Day". That solves the problem of using Cast(). BTW, why is there no "Interval Week" column type? There are a lot of things that are measured in weeks. TIA Barry |
Fri, Aug 3 2012 6:46 PM | Permanent Link |
Fernando Dias Team Elevate | Barry,
<<why is there no "Interval Week" column type?>> I don't know, perhaps because INTERVAL '1' WEEK would be the same as INTERVAL '7' DAY. About the interval types, you can find all the interval types here: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Interval_Types -- Fernando Dias [Team Elevate] |
Wed, Sep 5 2012 7:53 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Barry,
<< 1) What confuses me is this syntax works: select date '2012-01-01' + interval '5' day from table1 2) but replacing the '5' with an integer column name, won't work: select date '2012-01-01' + interval day_num day from table1 >> Column references have their own type, etc. whereas '5' would be considered just a normal string without the INTERVAL modifier in front of it. IOW, the INTERVAL, DATE, TIME, and TIMESTAMP modifiers are simply a way to tell the SQL compiler "Hey, this isn't just a string, it's something else". Therefore, they are of little use for columns/parameters that have their own type already, and are invalid when you attempt to use them for type conversion instead of CAST. If you have any other questions, please let me know. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |