Is there a better way to code this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is there a better way to code this?

Author

Topic

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-12-07 : 12:28:47

I am trying to account for several situations with start and end date ranges. I am storing time values with my dates and I need to return anything that

Started on or before a certain date and ended on or after or not ended at all

Here is some sample code. I think I could write this a better way


DECLARE @datetime datetime

SELECT @datetime = '2007-08-28 09:28:42.803'

DECLARE @temp TABLE
( 
	StartDate datetime
	,EndDate datetime
	,ShouldBeReturned bit
)

INSERT INTO @temp ( StartDate, EndDate, ShouldBeReturned )

SELECT DATEADD(d,-3,@datetime), DATEADD(d,1,@datetime),1 UNION ALL --should return
SELECT DATEADD(d,-3,@datetime), NULL,1 UNION ALL --should return
SELECT DATEADD(d,-2,@datetime), DATEADD(d,-1,@datetime),0 UNION ALL  --NOT
SELECT DATEADD(d,-1,@datetime), DATEADD(hh,-5,@datetime),1 UNION ALL --SHOULD
SELECT @datetime , DATEADD(d,1,@datetime),1UNION ALL -- SHOULD
SELECT @datetime , NULL,1 UNION ALL --SHOULD
SELECT DATEADD(d,1,@datetime), NULL,0 -- SHOULD not

SELECT * FROM @TEMP

SET @datetime = DATEADD(d,DATEDIFF(d,0,@datetime),0)

SELECT * , DATEADD(ms,86399997,@DATETIME)
FROM @temp
	WHERE 
		(DATEADD(ms,86399997,@DATETIME) BETWEEN StartDate AND EndDate )
		OR 
		(StartDate <= DATEADD(ms,86399997,@DATETIME) AND EndDate IS NULL) 
		OR 
		(DATEADD(d, DATEDIFF(d, 0, @DATETIME), 0) BETWEEN StartDate AND EndDate )

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-12-07 : 14:24:52

How about this?

SELECT *
FROM @temp
WHERE @DATETIME >= DATEADD(d,DATEDIFF(d,0,startdate),0)
and @DATETIME <= ISNULL(DATEADD(d,DATEDIFF(d,-1,enddate),0) ,'12/31/2099')

Jim

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-12-07 : 15:00:33

Thanks, that works good and looks cleaner. I did want to try and avoid putting functions on the table columns but based on the fact I have so many OR conditions I am probably not going to lose much.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources