| Author |
 Topic  |
|
|
magmo
Constraint Violating Yak Guru
465 Posts |
 Posted - 01/08/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 01/08/2013 : 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
India
22461 Posts |
Posted - 01/08/2013 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 01/08/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 01/08/2013 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 01/08/2013 : 03:32:01
|
| Yes but is that beacuse the datetime is only accurate to 3ms? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47152 Posts |
|
| |
Topic |
|
time portion
