First day and Last day of any Month using T-SQL
How to Get the First and Last Date of the Previous Month in SQL
Generating reports for the previous month often requires knowing the first and last dates of that month. Here’s a simple SQL script to help you achieve this.
SQL Script
DECLARE @CurrentDate DATETIME;
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @CurrentDate = GETDATE();
-- Calculate the first date of the previous month
SELECT @StartDate = DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, @CurrentDate)) - 1), DATEADD(MONTH, -1, @CurrentDate));
-- Calculate the last date of the previous month
SELECT @EndDate = DATEADD(SECOND, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentDate), 0));
-- Convert dates to a specific format
SELECT @StartDate = CONVERT(VARCHAR(12), @StartDate, 112), @EndDate = CONVERT(VARCHAR(12), @EndDate, 112);
-- Output the results
SELECT @StartDate AS FirstDate, @EndDate AS LastDate;
Explanation
In this script, we declare three variables: @CurrentDate
, @StartDate
, and @EndDate
. While @CurrentDate
is used to set the current date, it can be omitted if not needed. The script calculates the first and last dates of the previous month and formats them for easy use in reports.
Reusability
You can create a function to reuse this logic across your project, making it easier to generate monthly reports consistently.
- Submitted By Vibhuti Singh
- Category ms-sql
- Created On 22-Aug-2024