Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Date Math |
Wed, Jun 19 2013 9:33 AM | Permanent Link |
Ben Sprei CustomEDP | What would be the correct syntax to get the number of days between two
dates. Im trying to get the age of an invoice by subtracting the date of an invoice from the current date. Also it would be help ful if a certain date can be stored in a parameter and the Invoice date be subtracted from the date stored in the param. |
Wed, Jun 19 2013 10:04 AM | Permanent Link |
Fernando Dias Team Elevate | Ben,
Here is an example: SELECT (CURRENT_DATE - InvoiceDate) AS InvoiceAge FROM Invoices -- Fernando Dias [Team Elevate] Em 19/06/2013 14:33, Ben escreveu: > What would be the correct syntax to get the number of days between two > dates. Im trying to get the age of an invoice by subtracting the date of an > invoice from the current date. > Also it would be help ful if a certain date can be stored in a parameter and > the Invoice date be subtracted from the date stored in the param. > > |
Wed, Jun 19 2013 10:16 AM | Permanent Link |
Fernando Dias Team Elevate | Ben,
About the use of a parameter in the SELECT list, it's not allowed (at least for now), the only solution i can see is to generate the SQL statement 'on-the-fly' injecting the date you want in it. -- Fernando Dias [Team Elevate] |
Wed, Jun 19 2013 10:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
>What would be the correct syntax to get the number of days between two >dates. Im trying to get the age of an invoice by subtracting the date of an >invoice from the current date. Age in days is simply Column2 - Column1 Not sure just what datatype it ends up as though - probably INTERVAL >Also it would be help ful if a certain date can be stored in a parameter and >the Invoice date be subtracted from the date stored in the param. That's allowed in the WHERE clause but not the SELECT clause. eg WHERE Column2 - :DateTest > 5 Roy Lambert [Team Elevate] |
Wed, Jun 19 2013 10:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
>What would be the correct syntax to get the number of days between two >dates. Im trying to get the age of an invoice by subtracting the date of an >invoice from the current date. Age in days is simply Column2 - Column1 >Also it would be help ful if a certain date can be stored in a parameter and >the Invoice date be subtracted from the date stored in the param. That's allowed in the WHERE clause but not the SELECT clause. eg WHERE ( :Test - Column2) < 90 For a fuller understanding of what's going on search for INTERVAL in this ng Roy Lambert [Team Elevate] |
Wed, Jun 19 2013 10:34 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
<< Not sure just what datatype it ends up as though - probably INTERVAL >> Yes, the type is INTERVAL DAY. BTW, if I remember well it was you who asked for this feature recently, right? It would be a nice feature... -- Fernando Dias [Team Elevate] |
Mon, Jun 24 2013 4:03 PM | Permanent Link |
Ben Sprei CustomEDP | How about in a case statement like this:
Select *, Case when (:AgeDate - InvDate < 30) then (AmtInv) as Current end from APLines This gives you an error 700 - Dinamic Param Ref not allowed. I gues the only way around this is to either plug the date with a format statement or use a query with as many unions as categories. What a bummer "Roy Lambert" <roy@lybster.me.uk> wrote in message news:5D78C8A8-743E-4170-A4BF-98DA81343948@news.elevatesoft.com... > Ben > > >>What would be the correct syntax to get the number of days between two >>dates. Im trying to get the age of an invoice by subtracting the date of >>an >>invoice from the current date. > > Age in days is simply Column2 - Column1 > > >>Also it would be help ful if a certain date can be stored in a parameter >>and >>the Invoice date be subtracted from the date stored in the param. > > That's allowed in the WHERE clause but not the SELECT clause. > > eg > > WHERE ( :Test - Column2) < 90 > > > For a fuller understanding of what's going on search for INTERVAL in this > ng > > > Roy Lambert [Team Elevate] |
Mon, Jun 24 2013 5:26 PM | Permanent Link |
Adam Brett Orixa Systems | I think this is a case where VIEWS can be useful.
SELECT the calculated column into the view (as an INTERVAL DAY), and then add the parameter in a second SELECT (perhaps in a STORED PROC) which uses the View. I am pretty sure this would generate the result you need. |
Wed, Jul 3 2013 5:02 PM | Permanent Link |
Ben Sprei CustomEDP | Creating the view correctly will require the same type of calculation. I
guess I will use a format statement bfore running the query. Its only 4 lines to modify. |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |