SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 time portion
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

524 Posts

Posted - 01/08/2013 :  01:29:37  Show Profile  Reply with Quote
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
8765 Posts

Posted - 01/08/2013 :  02:18:06  Show Profile  Visit webfred's Homepage  Reply with Quote
Please give an example because there are many ways...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 01/08/2013 :  02:22:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

magmo
Aged Yak Warrior

524 Posts

Posted - 01/08/2013 :  02:29:01  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 01/08/2013 :  02:38:11  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

magmo
Aged Yak Warrior

524 Posts

Posted - 01/08/2013 :  03:32:01  Show Profile  Reply with Quote
Yes but is that beacuse the datetime is only accurate to 3ms?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/08/2013 :  03:38:28  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000