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.

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.
>
>

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]

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]

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]

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]

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]

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.

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.