Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Calculating 30 days from end of month using SQL |
Mon, Jul 16 2012 10:15 PM | Permanent Link |
Adam H. | 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. |
Tue, Jul 17 2012 3:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Jul 17 2012 5:22 AM | Permanent Link |
John Hay | 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 |
Tue, Jul 17 2012 1:34 PM | Permanent Link |
John Hay | 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 |
Tue, Jul 17 2012 5:34 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Wed, Jul 18 2012 4:26 AM | Permanent Link |
John Hay | 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 |
Wed, Jul 18 2012 9:11 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | "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 |
Sun, Jul 22 2012 9:19 PM | Permanent Link |
Adam H. | Hi Gentlemen,
Sorry for the delay in getting back to you. This is definitely what I'm needing. Thanks for your help! Cheers Adam |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |