CODEWITHSUNDEEP
sqlsql-server
Chin
Chin

Reputation: 1

How to get the first day of a half year in SQL?

To get the last day of a semester, I am using the below query:

select dateadd(quarter, datediff(quarter, 0, @mydate) / 2 * 2 + 2, -1)

I want to use a similar approach to get the first day of half year based on a given date.

For example, if my date is 5th of March 2024, then the SQL should return 1st of January 2024.
And if my date is 15th of November 2024. then the SQL should return 1st July 2024. Any help please?

Upvotes: 0

Views: 204

Answers (1)

Thom A
Thom A

Reputation: 95924

The simplest method, in modern versions of SQL Server, would be to just use DATE_BUCKET:

SELECT DATE_BUCKET(MONTH, 6, GETDATE());

If you aren't on 2022+, you'll need to use the "old" DATEADD/DATEDIFF method. The /6 * 6 makes use of integer division. So, for example, 10 / 6 * 6 = (10 / 6) * 6 = (~1.66 ≈ 1) * 6 = 1 * 6 = 6

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) / 6 * 6,0);

Upvotes: 2

Related Questions