SQLTeam.com | Weblogs | Forums

Get 1st-July-Previous year in MS SQL Server

Hi Team,

How to get 1st-July of previous year?

I have a requirement where I need start extracting data from 1st-July-Previous year till end of current month.

For example,

on 11/2/2021, I need to extract data from 07/01/2020 till 11/30/2021.
on 02/05/2022, I need to extract data from 07/01/2021 till 02/28/2022.

I was able to find how to get last day of the current month but could not find solution on getting 1st-July-Previous year.

why for 11/2/2021, you have 07/01/2020 till 11/30/2021. Wouldn't the previous July be 7/1/2021?

declare @startdate date,
@enddate date,
@today date = GetDate() --'2/5/2022'--
,@adjustment int
set @adjustment = case when month(@today) < 7
then 1
when month(@today) > 7
then 0
else
0
end

set @startdate = cast('07/01/' + cast(year(@today) - @adjustment as char(4)) as date)
set @enddate = EOMonth(@today)

select @StartDate, @EndDate

1 Like
SELECT *
FROM YourTable
WHERE YourDate >= DATEADD(month, 6, DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP) - 1, 0))
	AND YourDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0);

If you need a date data type: datefromparts(year(getdate()) - iif(month(getdate()) < 7, 1, 0), 7, 1)
If you need a datetime data type: datetimefromparts(year(getdate()) - iif(month(getdate()) < 7, 1, 0), 7, 1, 0, 0, 0, 0)