SQL Server coming February 1st
Could you help find the closest February 1 to the current date?
The following script only works correctly if it is run in the current year:
select DATEADD(MONTH,1,DATEADD(year, DATEDIFF(year, -1, GETDATE()), 0))
But when it launches next January, it will lead to the wrong result.
Thank you in advance!
source to share
Try something like this with IIF
andDATEFROMPARTS
select IIF(month(getdate()) > 2,
DATEFROMPARTS(YEAR(Getdate()),2,1),DATEFROMPARTS(YEAR(Getdate())-1,2,1) )
If you are using older versions then
select Case When month(getdate()) > 2
then CAST(CAST(YEAR(getdate()) as char(4))+'-02-01' as date)
else CAST(CAST(YEAR(getdate()) - 1 as char(4))+'-02-01' as date)
end
source to share
Your question can be interpreted in two different ways: the first would be to search for the next closest February 1 (i.e. Feb 2 2016
will return Feb 1 2017
), and the second would be to find the closest February 1 to the current date (i.e., Feb 2 2016
will return Feb 1 2016
).
Prdp has already provided an answer for the former, so this approach (which I am sure can be simplified) would be for the latter.
It will consider the current year Feb 1 and the next year Feb 1 and calculate DateDiff()
in days from both and select the closest one.
;With Dates As
(
Select ThisFeb = DateFromParts(Year(GetDate()), 2, 1),
NextFeb = DateFromParts(Year(GetDate()) + 1, 2, 1)
), Distance (Date, Distance) As
(
Select ThisFeb, Abs(DateDiff(Day, GetDate(), ThisFeb)) As ThisFebDiff
From Dates
Union All
Select NextFeb, Abs(DateDiff(Day, GetDate(), NextFeb)) As NextFebDiff
From Dates
)
Select Top 1 Date
From Distance
Order By Distance Asc
source to share
This is another possible solution ...
DECLARE @SomeDate DATE = '2017-06-30';
SELECT TOP 1
ClosestFebFirst = CASE WHEN dd.Diff1 < dd.Diff2 THEN pd.D1 ELSE pd.D2 END
FROM
( VALUES (
DATEFROMPARTS(YEAR(@SomeDate), 2, 1),
DATEFROMPARTS(YEAR(@SomeDate) + 1, 2, 1)
)
) pd (D1, D2)
CROSS APPLY ( VALUES (
ABS(DATEDIFF(dd, @SomeDate, pd.D1)),
ABS(DATEDIFF(dd, @SomeDate, pd.D2))
)
) dd (Diff1, Diff2);
source to share