Search code examples
sqlsql-serverdatediffdateaddgetdate

Create a dynamic date range in SQL

I'm a new learner in SQL and trying to create a dynamic date range based on the current month. For instance, if the current month is June, then the start date should be May 1st and the end date should be May 31st, etc. Here is the code:

For start date:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)

For end date:

DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, 0)

I don't quite understand the meaning of above codes. But I ran it and the outputs seemed not correct. I'm wondering if the above codes realize what I need.

Many thanks to your help!

Solution

  • In SQL Server, you can use eomonth():

    select dateadd(day, 1, eomonth(getdate(), -2)) as start_date,
       eomonth(getdate(), -1) as end_date

    Here is a db<>fiddle.