Comparing Date Column to Date Range

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)

Comparing Date Column to Date Range

Author

Topic

katalystguy
Starting Member

20 Posts

Posted - 2012-09-17 : 13:06:57

Gents,

Any advice on whay the following queery fails to select records where the date column SEGStart = 01/08/2012. If I change the lower date range value from '28/07/2012' to '01/08/2012' then it works?

Select
*
From
Import_ACD_Call_Details
Where
ANSLogin = 25108
AND (Convert(varchar(10), SEGStart, 103) >= '28/07/2012' AND Convert(varchar(10), SEGStart, 103) <= '03/08/2012')
Order By
CallID, Segment

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 13:09:10

no need of convertion

just use like

SEGStart >='20120728'
AND SEGStart < '20120803'

see

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

katalystguy
Starting Member

20 Posts

Posted - 2012-09-17 : 13:23:38

That worked, but how does it handle the time value as this is a datetime field with varying time values?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 14:27:11

when you specify date it regards it as start of day (12:00 midnight)

so using >= and <
make sure it includes all values from 12:00 midnight of start date to

11:59:59:... PM of enddate -1 ie just before midnight of end date

reason why this is possible is because dates are internally stored as numeric value in sql server

see

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

katalystguy
Starting Member

20 Posts

Posted - 2012-09-18 : 06:01:00

Okay, I am running this query in SSIS Exexute SQL Task and the date field will be passed as a datetime variable. This is why I was using convert. How would this best be completed in SSIS?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 11:00:35

you can pass them as varchar but make sure you use >= and < logic to ensure you've whole interval covered as in my example @ 09/17/2012 : 13:09:10 above. there also i'm passing values as varchar only (note ' around values)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources