As I’ve mentioned before I write a fair bit of SQL code for various projects or support calls and will be posting some of it here.
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 (https://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))