Forum Discussion

JustasG's avatar
JustasG
Copper Contributor
Oct 18, 2023

Rolling year starting from specific date

Hi, I am trying to write SQL query where it would show year starting from 01st of June and ends on 31st of May.

This is what my query is at the moment: (dbo.TABLE.AttDate >= GETDATE() - 365) AND (dbo.TABLE.AttDate <= GETDATE())

Please let me know if there is a way to write this query.

Thanks

Business Intelligence

2 Replies

Sort By
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Oct 19, 2023

    JustasG , some notes:

     

    - Don't substract dates, use DATEADD (Transact-SQL) - SQL Server | Microsoft Learn

    - GETDATE() returns a time potion as well, if your date values don't hab´ve one, records will be missing in the result

    - Use alias for object to shorten query / for better readable

    - If you want to filter on fix dates, use fix dates and as it's best in neural date format like ISO

    => 

    SELECT *
FROM dbo.TABLE AS T
WHERE T.AttDate BETWEEN '20220601' AND '20230531'

     

    • JustasG's avatar
      JustasG
      Copper Contributor
      Oct 19, 2023
      olafhelper Hi, the only problem with your query is that it is fixed for 2022 and 2023, so I would have to go and change date every year. I don't know how to write query where you wouldn't need to adjust year manually.

Resources