| 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 |
|
|
|
time portion