time portion - SQL Server Forums

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)

time portion

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-01-08 : 01:29:37

When I filter based on dates on a datetime column I remove the time portion, but why is that important in order to get correct results?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-01-08 : 02:18:06

Please give an example because there are many ways...

Too old to Rock'n'Roll too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-01-08 : 02:22:26

quote:
Originally posted by magmo

When I filter based on dates on a datetime column I remove the time portion, but why is that important in order to get correct results?

In order to include all time, you need to exclude time part. For example if you want to get data of Dec 10, 2012, you have to write

WHERE date_col>='20121210' and date_col<'20121211'

to include all time for Dec 10, 2012

Madhivanan

Failing to plan is Planning to fail

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-01-08 : 02:29:01

I use this approach...

CREATE PROCEDURE dbo.uspTest
(
@FromDate DATETIME,
@ToDate DATETIME
)
AS

SET NOCOUNT ON

DECLARE @Temp DATETIME

IF @FromDate > @ToDate
SELECT @Temp = @FromDate,
@FromDate = @ToDate,
@ToDate = @Temp

SELECT @FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @FromDate), '19000101'),
@ToDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', @FromDate), '19000101')

SELECT COUNT(ReportID) AS Qty
FROM dbo.tbl_Error_Reports
WHERE DateReported >= @FromDate
AND DateReported < @ToDate

It works fine but I was looking for a detailed explanation of why its so important to remove the time portion from a datetime column when searching between dates

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-01-08 : 02:38:11

quote:
Originally posted by magmo

I use this approach...

CREATE PROCEDURE dbo.uspTest
(
@FromDate DATETIME,
@ToDate DATETIME
)
AS

SET NOCOUNT ON

DECLARE @Temp DATETIME

IF @FromDate > @ToDate
SELECT @Temp = @FromDate,
@FromDate = @ToDate,
@ToDate = @Temp

SELECT @FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @FromDate), '19000101'),
@ToDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', @FromDate), '19000101')

SELECT COUNT(ReportID) AS Qty
FROM dbo.tbl_Error_Reports
WHERE DateReported >= @FromDate
AND DateReported < @ToDate

It works fine but I was looking for a detailed explanation of why its so important to remove the time portion from a datetime column when searching between dates

In this case the developer wanted to make sure not to use any time part even if the caller has given a time part via parameter.

Too old to Rock'n'Roll too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-01-08 : 03:32:01

Yes but is that beacuse the datetime is only accurate to 3ms?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-08 : 03:38:28

quote:
Originally posted by magmo

Yes but is that beacuse the datetime is only accurate to 3ms?

Yes and result may miss records that occur towards either boundaries

also see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources