Hi,

I'm currently trying to perform an SQL operation where I calculate the
due date of an invoice. The due date of the invoice will be 30 days
after the end of the month from the date the transaction was initiated.

In the past, I have had to do a similar thing, but calculating it from
the end of the week. To arrive at the correct 'end of the week' date I
have used the following:

Select (cast(T.DateTime as date)- extract(dayofweek from T.DateTime) +
7) as EOW

This works quite fine. However now I'm at a stage where  I need to
calculate the end of the month, and unlike a week - months have
different amount of days in them.

I was wondering if anyone could recommend a solution to perform this
operation in DBISAM using SQL?

Cheers

Adam.

Adam


You need to extract the month and then use that in conjuction with a series of IFs or a CASE. I always have to look at a calendar to see how many days in a month so I'm not going to try and write it

Roy Lambert [Team Elevate]

Adam

> I'm currently trying to perform an SQL operation where I calculate the
> due date of an invoice. The due date of the invoice will be 30 days
> after the end of the month from the date the transaction was initiated.
>
>
> This works quite fine. However now I'm at a stage where  I need to
> calculate the end of the month, and unlike a week - months have
> different amount of days in them.
>
> I was wondering if anyone could recommend a solution to perform this
> operation in DBISAM using SQL?

30 days after the end of the month is 29 days after the start of the next month so how about something like

SELECT Invdate,
CAST(IF(EXTRACT(MONTH FROM Invdate) < 12 THEN
 CAST(YEAR AS CHAR(4))+'-'+RIGHT('0'+CAST(EXTRACT(MONTH FROM Invdate)+1 AS CHAR(2)),2)+'-01'
ELSE
 CAST(EXTRACT(YEAR FROM Invdate)+1 AS CHAR(4))+'-01-01') AS DATE)+29

John

oops - that should be

SELECT Invdate,
CAST(IF(EXTRACT(MONTH FROM Invdate) < 12 THEN
 CAST(EXTRACT(YEAR FROM Invdate) AS CHAR(4))+'-'+RIGHT('0'+CAST(EXTRACT(MONTH FROM Invdate)+1 AS CHAR(2)),2)+'-01'
ELSE
 CAST(EXTRACT(YEAR FROM Invdate)+1 AS CHAR(4))+'-01-01') AS DATE)+29 AS DateDue
FROM Atable

John

Hi John

I took your code and tried it out on my *DBISAM3* and it didn't work because of the RIGHT() function, so I tinkered with it a little and got it going BUT note, I changed the "< 12" to "< 11".  Ran it on one of my app tables and it seems to work correctly.
================
SELECT DISTINCT TransDate,
      CAST(IF(EXTRACT(MONTH FROM TransDate) < 11
           THEN CAST(EXTRACT(YEAR FROM TransDate) AS CHAR(4))
           + '-' + CAST(EXTRACT(MONTH FROM TransDate)+1 AS CHAR(2))+'-01'
           ELSE CAST(EXTRACT(YEAR FROM TransDate)+1 AS CHAR(4))+'-01-01')
      AS DATE)+29 AS DateDue,
      IF(EXTRACT(MONTH FROM TransDate) < 11
      THEN  CAST(EXTRACT(MONTH FROM TransDate)+1 AS CHAR(2))
      ELSE '1')
      AS MonthCalc
FROM AdminTrans
====================

"John Hay" wrote:

oops - that should be

SELECT Invdate,
CAST(IF(EXTRACT(MONTH FROM Invdate) < 12 THEN
 CAST(EXTRACT(YEAR FROM Invdate) AS CHAR(4))+'-'+RIGHT('0'+CAST(EXTRACT(MONTH FROM Invdate)+1 AS CHAR(2)),2)+'-01'
ELSE
 CAST(EXTRACT(YEAR FROM Invdate)+1 AS CHAR(4))+'-01-01') AS DATE)+29 AS DateDue
FROM Atable

John

Jeff

I had not realised that DBISAM was happy with "date strings" like  2012-1-5.  I had assumed that you needed 2012-01-05 -
you learn something new every day

It does need < 12 otherwise dates in November give a due date as January  the following year.

John

"John Hay" wrote:
.
.
.
It does need < 12 otherwise dates in November give a due date as January  the following year.
.

Correct - it would help if I read my test results properly
Aspect Systems Ltd - New Zealand

Hi Gentlemen,

Sorry for the delay in getting back to you. This is definitely what I'm
needing.

Thanks for your help!

Cheers

Adam