DECLARE @last date;
DECLARE @now date;
SET @now = getdate();
SET @last = DATEADD(month,-1, GETDATE())
SELECT @last as last ,@now as now
and that I get data last 2023-05-09 and now 2023-06-09
I want to format date like last 2023-06-01 and now 2023-06-09
>Solution :
Yes, you can use the DATEFROMPARTS function in SQL Server to format the date to the first day of the month. Here is an example of how to modify your query to format the dates as the first day of the month:
DECLARE @last date;
DECLARE @now date;
SET @now = GETDATE();
SET @last = DATEADD(month,-1, GETDATE());
SELECT DATEFROMPARTS(YEAR(@last), MONTH(@last), 1) AS last, DATEFROMPARTS(YEAR(@now), MONTH(@now), 1) AS now;
In this modified query, the DATEFROMPARTS function is used to construct a new date value using the year and month components of the original date, and setting the day component to 1. This effectively formats the date value to the first day of the month.
The result of this query will be something like:
last now
2023-05-01 2023-06-01
Note that the day component of the date is always set to 1 in this modified query. If you want to format the date to a specific day of the month, you can replace the 1 parameter in the DATEFROMPARTS function with the desired day value.