Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
SQL Snippet: Get Dates for Accruals
September 4th, 2018 by Ian Grieve
I recently created a report for a client to use to extract transaction lines to use to import as an accruals journal; as part of the extract I worked out the last day of the one month and the first day of the next to use as the transaction and reversing dates on the journal.
The scripts below has versions for both before and after SQL 2012 (with the introduction of the EOMONTH function in 2012, getting these dates became easier).
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ -- set date variable DECLARE @Date DATETIME = GETDATE() -- get last date of this month SELECT CONVERT(VARCHAR(10), DATEADD(month, ((YEAR(@Date) - 1900) * 12) + month(@Date), -1), 126) -- get last date of this month in SQL 2012 SELECT EOMONTH(@Date) -- get first date of next month SELECT CONVERT(VARCHAR(10), DATEADD(month, DATEDIFF(month, 1, DATEADD( month, 1, @Date )), 0), 126) -- get first date of next month in SQL 2012 SELECT DATEADD(day, 1, EOMONTH(@Date))