Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Stairway to Data, Step 4: Temporal Data Expand / Collapse
Author
Message
Posted Thursday, June 16, 2011 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:13 PM
Points: 6, Visits: 134
Hi Joe. Great series of articles!

One nit: the earth rotates in a "day" and takes a "year" to *orbit* the sun (not rotate around the sun).
Post #1126536
Posted Friday, June 17, 2011 10:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2014 5:07 PM
Points: 25, Visits: 67
WHERE my_event_start BETWEEN '20100601' and '20100630 23:59:59.999'

?
Well frankly, no again. TSQL can do weird rounding things to milliseconds because it's granularity is coarser than the millisecond. I'm pretty sure .999 will be rounded to the next second which is 20100701 00.00.000. So what's the answer?

Don't use BETWEEN when dealing with datetimes. Use:
WHERE my_event_start >= '20100601' and my_event_start < '20100701'

Edit question: The above should say ">=" and "<". How do I make the IFcode shortcuts cooperate?


Actually, in my experience, the granularity is 3 milliseconds. You can test this by using;

PRINT CONVERT(VARCHAR, CAST('01/01/2011 23:59:59.998' AS DATETIME), 121)
PRINT CONVERT(VARCHAR, CAST('01/01/2011 23:59:59.999' AS DATETIME), 121)

It appears that .999 .000 and .001 are all .000

I tend to use .997 as the endtime just in case the .998 occasionally drifts into the .000 bucket and have always found it reliable, giving;

WHERE my_event_start BETWEEN '20100601' AND '20100630 23:59:59.997'

Post #1127516
Posted Friday, June 17, 2011 10:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:21 AM
Points: 1,793, Visits: 5,044
The BETWEEN operator is problematic when used with datetime, floating point numbers, and other non-discrete data types. When selecting rows where a date falls within a range, I'll use the following method:

where enrollment_date >= '2011-06-01' and enrollment_date < '2011-07-01';
Post #1127533
Posted Sunday, June 26, 2011 4:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, December 7, 2014 3:35 PM
Points: 65, Visits: 446
Good article, but is the political correctness really necessary? The use of the term "Common Era" to describe the Christian-based calendar is offensive. So is the comment about not using "Ed the Gregorian"'s calendar (who?) anymore. The tone that "we" don't use the Christian calendar "any more" is especially offensive to Christians and other groups who use custom calendars for whatever reason.

Jay Bienvenu | http://bienv.com | http://twitter.com/jbnv
Post #1131831
Posted Sunday, June 26, 2011 5:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2014 5:07 PM
Points: 25, Visits: 67
jbnv (6/26/2011)
Good article, but is the political correctness really necessary? The use of the term "Common Era" to describe the Christian-based calendar is offensive. So is the comment about not using "Ed the Gregorian"'s calendar (who?) anymore. The tone that "we" don't use the Christian calendar "any more" is especially offensive to Christians and other groups who use custom calendars for whatever reason.


The author is discussing Temporal data with reference to international standards. Those generally use non-denominational terminology. Why would it offend anyone that international standards do not use the Christian terminology ?

FWIW, it's not like the traditional BC/AD distinction is accurate since the consensus of theological opinion is that Joshua Ben Yosef was probably actually born either around 4BC (Herod's death) or 6AD (the first Census) during Spring.

As for the reference to "Ed the Gregorian", if you read the article, you will see that the actual phrase used is "it is not call Ed the Gregorian Calendar" and obviously was just a misplaced space that changed "called" into "call Ed".
Post #1131839
Posted Sunday, June 26, 2011 5:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2014 5:07 PM
Points: 25, Visits: 67
Eric M Russell (6/17/2011)
The BETWEEN operator is problematic when used with datetime, floating point numbers, and other non-discrete data types. When selecting rows where a date falls within a range, I'll use the following method:

where enrollment_date >= '2011-06-01' and enrollment_date < '2011-07-01';


My comment explained how the BETWEEN operator can be reliably used for datetime data types. Personally, I find that the BETWEEN operator makes more sense than using a combination of >= and <.

The approach you use also should really be in brackets just in case...

where enrollment_date >= '2011-06-01' and enrollment_date < '2011-07-01'
or order_status = 'Failed';

could equate to

where enrollment_date >= '2011-06-01' and (enrollment_date < '2011-07-01'
or order_status = 'Failed');

rather than

where (enrollment_date >= '2011-06-01' and enrollment_date < '2011-07-01')
or order_status = 'Failed';

(I haven't got SQL available to check... I'm just paranoid, probably, but it has stood me in good stead so far !)

The same underlying issues apply with other non-discrete data types... it is slightly easier to specify a floating point comparion greater than 0 and less than 10 using your approach than with a BETWEEN but it actually is probably not reliable. Instead, I tend to just tweak the numbers to include whatever precision I need.

Thus, if I am using currency to the accuracy of cents, I use WHERE float_number BETWEEN 0.001 AND 9.999, which works perfectly and reliably.

In practice, so long as we have the tools available to do the job right, the choices used are more a matter of personal preference than anything else...
Post #1131841
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse