select a record based on a time range

Question

Post reply

select a record based on a time range

SQLDCH

SSChampion

Points: 11627
More actions
October 29, 2010 at 2:54 pm

#242464

I'm sure this has been asked before, but I can't find it anywhere. I want to select a record based on it's time, searching between two different times.
For instance, I want all backupsets that ran between 2:00am and 3:00am, yesterday:
select database_name, backup_finish_date, backup_size, type
from msdb..backupset
where backup_finish_date between '2010-10-28 02:00:00.000' and '2010-10-28 03:00:00.000'
This works, but I need it to be variable so I don't have to change the criteria each time I run the query. I tried something like this:
declare @date1 varchar(50)
declare @date2 varchar(50)
set @date1 = cast(convert(varchar(10), getdate(), 120) + ' 02:00:00.000' as datetime)
select database_name, backup_finish_date, backup_size, type
from msdb..backupset
where backup_finish_date between @date1 and '2010-07-27 03:00:00.000'
but that obviously doesn't work. Would someone share some wisdom? This is SQL Server 2005, btw.
TIA.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Yeah, well...The Dude abides.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why does that 'obviously not work'? It's almost what you want.

DECLARE @date1 DATETIME

DECLARE @date2 DATETIME

SET @date1 = DATEADD(mi, 120, DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())-1)) -- yesterday at 2:00 am

SET @date2 = DATEADD(mi,60,@date1)

SELECT database_name ,

backup_finish_date ,

backup_size ,

type

FROM msdb..backupset

WHERE backup_finish_date BETWEEN @date1 AND @date2

Or have I misunderstood requirements?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLDCH SSChampion Points: 11627 More actions · Answer 2

Thanks Gail...I needed the logic of the DATEADD / DATEDIFF functions. The way I set my '@date1' variable was my problem as I didn't know how to calculate those times.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 3

From a performance standpoint, are you better off putting the calcs on GETDATE() directly in the SQL rather than using variables? I would hope that SQL's optimizer would realize then that it's dealing with a constant value and build the query plan accordingly.

Scott Pletcher, SQL Server MVP 2008-2010

drew.allen SSC Guru Points: 76984 More actions · Answer 4

GilaMonster (10/29/2010)
DECLARE @date1 DATETIME DECLARE @date2 DATETIME SET @date1 = DATEADD(mi, 120, DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())-1)) -- yesterday at 2:00 am SET @date2 = DATEADD(mi,60,@date1)

I'm curious about why you used minutes rather than hours for the calculations. I tend to use larger units, because they give smaller numbers (and, thus, require less typing).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

drew.allen (10/29/2010)
I'm curious about why you used minutes rather than hours for the calculations.

Because I initially misread the requirements and though he wanted from 2:30 to 3. Given the half hour, it was easier to use minutes (150) than both a dateadd(hh and a dateadd (mi. When I realised it was easier just to change the numbers rather than the whole thing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Some more datatime tricks...

DECLARE @FromDate DATETIME,

@ToDate DATETIME

SELECT@FromDate = DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), '02:00'),

@ToDate = DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), '03:00')

SELECT Database_Name,

Backup_Finish_Date,

Backup_Dize,

[Type]

FROM msdb..BackupSet

WHERE Backup_Finish_Date BETWEEN @FromDate AND @ToDate

N 56°04'39.16"
E 12°55'05.25"