Beginning of period calculations are common things. Rolling month reports, queries to pull everything from last month etc require beginning of period calculations. Well here is a very simple pattern to get them.

DECLARE @DateConst datetime
SET @DateConst = '1/1/2000'
SELECT DATEADD(mm, DATEDIFF(mm, @DateConst, DateCol), @DateConst)
FROM TableName

Dissecting it somewhat we are taking a DATEDIFF of a constant of type date (or datetime, datetime2 etc) and a column of type date (or datetime, datetime2 etc). We then add that DATEDIFF back to the constant. Now there are a few rules here.

• The constant has to come first but it doesn’t really matter how long ago or how far into the future it is.
• The increment should match the period you are working with. First day of the month the period would be mm, 10th day of the quarter the period should be qq, and for the previous Wednesday it would be ww. etc.
• The constant should also be similar to the value you want back. In other words if you want the 7th day of last month your constant will be the seventh day of a month, any month. If you want next Monday then your constant has to be a Monday, again, any Monday.

The example I gave above lists out the first day of the month for each value in DateCol. But let’s say you want to pull every row where the value in DateCol was from last month.

DECLARE @DateConst datetime
SET @DateConst = '1/1/2000'
SELECT *
FROM sys.databases
WHERE DateCol >= DATEADD(mm, DATEDIFF(mm, @DateConst, DateCol)-1, @DateConst)
  AND DateCol < DATEADD(mm, DATEDIFF(mm, @DateConst, DateCol), @DateConst) 

You may be wondering why I used >= and < instead of between. Well 2 reasons .. first calculating the beginning of a period is easier than calculating the end of one and by just using a < I don’t have to worry about end of month calculation problems. And number two, I’ve read Aaron Bertrand’s blog about what BETWEEN and the Devil have in common. That being said I hope you noticed how easy it was for me to pull the values I was looking for. It would be just as easy to do a running month calculation, a report with just values for the quarter etc.

In fact the thought for this particular post came from a recent experience. I need to delete 580 million rows from a table. I can only delete during a 2 hour window M-F and Saturday after 2PM till Monday before 6AM. By using the following code I can delete the rows in 100k transactions. In case you are interested I ran some tests to decide on the 100k value. 10k took 1 minute 100k took 3-4 minutes and 200k took 10-13 minutes. You can see the obvious winner here. So here is my delete code.

DECLARE @RowCount int
SET @RowCount = 1
WHILE @RowCount > 0
BEGIN
DELETE TOP (100000) FROM TableName
WHERE MyStatus = 'DeleteMe'
SET @RowCount = @@RowCount
END

The only problem is that I have to start and stop it manually, and given my short time frames I need every minute I can get. So I decided to create a job to run the code for me. Now the obvious problem here is that the code runs until there are no rows to delete. I need to modify it to end and a specific time. My window is 7-9PM so I start my job at 7PM and calculate 9PM and end my loop then. To do the calculation I get the beginning of the day then add 21 hours, like so:

DECLARE @RowCount int
SET @RowCount = 1
WHILE @RowCount > 0 
AND GETDATE() < DATEADD(hh, 21, DATEADD(dd,DATEDIFF(dd,'1/1/2000',GETDATE()),'1/1/2000'))
BEGIN
DELETE TOP (100000) FROM TableName
WHERE MyStatus = 'DeleteMe'
SET @RowCount = @@RowCount
END

That’s the code for the job that runs M-F. Now I need to create the job that starts on Saturday. I’ll need to calculate Monday at 6AM to end my loop. To do this calculation I get Monday of next week (Monday of this week + 1 week) and add 6 hours, like so:

DECLARE @RowCount int
SET @RowCount = 1
WHILE @RowCount > 0 
AND GETDATE() < DATEADD(hh, 6, DATEADD(ww,DATEDIFF(ww,'1/3/2000',GETDATE())+1,'1/3/2000'))
BEGIN
DELETE TOP (100000) FROM TableName
WHERE MyStatus = 'DeleteMe'
SET @RowCount = @@RowCount
END

One important point in this calculation. I used ‘1/3/2000’ as my date constant. This is because that particular date was a Monday. If I had used ‘1/1/2000’ I would have gotten Saturday which is when I want the job to start, not end.

Note that in both pieces of code I was able to use the same pattern and it was very easy to get exactly the times I needed. Certainly much easier than using the method of breaking out the date part of the datetime as a string, adding a string for the time, then converting the whole thing back to a datetime. That being one of several more complicated methods that I have used in the past.

Filed under: Microsoft SQL Server, Problem Resolution, SQL Agent Jobs, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, SQL Agent Jobs, sql statements, T-SQL