Contents
How to loop through dates in T-SQL?
Loop Through Dates in T-SQL … One Day at a Time! Hidden in my SQL Server 2016 Security Demo blog post is a neat T-SQL trick to loop through a date range day by day (check out the “2 – Oil&Gas RLS Demo – LoadTables.sql” script when you download the code)! But to give you a simpler example the gist of the code in the script is this: Download code!
When to add a date column to a SQL query?
You’ll have to make the query more complicated if your first data point is after the first of the month, of course, since you’ll need to pull the last known balance from the previous month. For more info on calendar tables, see this tip.
How to generate a list of dates in SQL Server?
Part 3 is specifically relevant as it deals with dates. Assuming you don’t have a Calendar table you can use the stacked CTE method to generate a list of dates between your start and end dates.
How to loop through a range of dates?
As FildFoundException replied, we can use the DATEADD function to loop through the table: Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have any feedback, please tell us.
Why are there no loops in SQL Server?
SQL is a set based language and loops should be a last resort. So the set based approach would be to first generate all the dates you require and insert them in one go, rather than looping and inserting one at a time. Aaron Bertrand has written a great series on generating a set or sequence without loops:
How to generate a set of dates without a loop?
So the set based approach would be to first generate all the dates you require and insert them in one go, rather than looping and inserting one at a time. Aaron Bertrand has written a great series on generating a set or sequence without loops: Part 3 is specifically relevant as it deals with dates.