From a real work example:

I get a weekly data file of patient alarm data. This data is collected continuously throughout the day. However, the data must be exported manually and is usually done in the middle of the day. So the first and last data samples in my set could be taken at 14:41 one week apart. Since I only want to have full days worth of day, I like to truncate the data at midnight – deleting any partial days from the data set.

Instead of having to manually look at the data and determining when to cut off my data, I wrote the following simple script.

This script takes advantage of SQL’s variable functionality. Notice you need to declare your variables ahead of time. One thing to keep in mind is that variables only exist within their scripting block. Meaning once you are done executing this block of code, the variables are dropped from memory and no longer available to further code blocks.

I set the variables value using an inline Select statement. The nested function in the Select statement reads like this:

 convert(date, (dateadd(dd,1,min(dateField))))

It is actually a combination of the following functions – by order of operation:

Dateadd()

dateadd(dd,1,min(dateField)) = add 1 day(dd) to the minimum date (min(dateField)) in the table ** the dateadd function always starts at 12:00 AM when adding or subtracting a day

Convert()

Convert the value from Dateadd() above to the Date format.

Finally:

Delete from Table1
where dateField not between @start and @end
go

This final block of coat deletes every record in NOT in between the dates represented by the @Start and @End variables

SQL Code

declare @start date
declare @end date

set @start = (select convert(date,(dateadd(dd,1,min(dateField)))) from Table1)
set @end = (select convert(date,(dateadd(dd,0,max(dateField)))) from Table1)

delete from Table1
where date not between @start and @end
go