id the_dt_value milli best_lt_next_day_explicit good_lt_next_day_implicit vbad_between_1 wrong_between_2 expensive_lt_as_convert_108 expensive_ltet_as_convert_108 wrong_ltet_as_date_next_day wrong_ltet_as_datetime_next_day vbad_ltet_23_59_59 vbad_ltet_day_gap_1 vbad_ltet_day_gap_2 vbad_ltet_day_gap_3
----- --------------------- -------- ---------------------------- ---------------------------- --------------------- --------------------- ------------------------------ -------------------------------- ------------------------------ ---------------------------------- --------------------- ---------------------- ---------------------- ----------------------
1 6/30/2013 11:59:59 PM 0 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM (null) (null) (null)
2 6/30/2013 11:59:59 PM 7 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM (null) 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM (null) (null) (null) (null)
3 6/30/2013 11:59:59 PM 507 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM (null) 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM (null) (null) (null) (null)
4 6/30/2013 11:59:59 PM 997 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM (null) 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM 6/30/2013 11:59:59 PM (null) (null) (null) (null)
5 7/1/2013 12:00:00 AM 0 (null) (null) (null) 7/1/2013 12:00:00 AM (null) (null) 7/1/2013 12:00:00 AM 7/1/2013 12:00:00 AM (null) (null) (null) (null)
6 7/1/2013 11:59:59 PM 0 (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null)
7 7/1/2013 11:59:59 PM 7 (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null)
8 7/1/2013 11:59:59 PM 507 (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null)
9 7/1/2013 11:59:59 PM 997 (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null)
So here's the (sql server) query...
select
id
, the_dt_value
, datepart(ms,the_dt_value) milli
/* the benchmark: produces correct division of datetimes and requires no conversion of the data (just of the evaluator) */
, case when the_dt_value < cast('2013/07/01' as datetime) then the_dt_value else null end best_lt_next_day_explicit
, case when the_dt_value < '2013/07/01' then the_dt_value else null end good_lt_next_day_implicit
/* the awful truth, just don't use between ... and */
, case when the_dt_value between cast('2013/06/01' as datetime) and cast('2013/06/30 23:59:59' as datetime) then the_dt_value else null end vbad_between_1
, case when the_dt_value between cast('2013/06/01' as datetime) and cast('2013/07/01' as datetime) then the_dt_value else null end wrong_between_2
/* produces correct division of datetimes but requires conversion of the data and of the evaluator. NB: Different convert options will produce different results, many wrong. */
, case when CONVERT(date,the_dt_value,108) < cast('2013/07/01' as datetime) then the_dt_value else null end expensive_lt_as_convert_108
, case when CONVERT(date,the_dt_value,108) <= cast('2013/06/30 23:59:59' as datetime) then the_dt_value else null end expensive_ltet_as_convert_108
/* just wrong, often in so many ways, look at the results */
, case when the_dt_value <= cast('2013/07/01' as date) then the_dt_value else null end wrong_ltet_as_date_next_day
, case when the_dt_value <= cast('2013/07/01' as datetime) then the_dt_value else null end wrong_ltet_as_datetime_next_day
, case when the_dt_value <= cast('2013/06/30 23:59:59' as datetime) then the_dt_value else null end vbad_ltet_23_59_59
, case when the_dt_value <= cast('2013/06/30' as datetime) then the_dt_value else null end vbad_ltet_day_gap_1
, case when the_dt_value <= cast('2013/06/30 23:59:59' as date) then the_dt_value else null end vbad_ltet_day_gap_2
, case when the_dt_value <= cast('2013/06/30' as date) then the_dt_value else null end vbad_ltet_day_gap_3
-- add your own approach if you like
from test_data
and here's the simple test data used:
with test_data as (
select 1 as id, cast('2013/06/30 23:59:59:000' as datetime) as the_dt_value union all
select 2 as id, cast('2013/06/30 23:59:59:005' as datetime) as the_dt_value union all
select 3 as id, cast('2013/06/30 23:59:59:505' as datetime) as the_dt_value union all
select 4 as id, cast('2013/06/30 23:59:59:995' as datetime) as the_dt_value union all
select 5 as id, cast('2013/07/01 00:00:00:000' as datetime) as the_dt_value union all
select 6 as id, cast('2013/07/01 23:59:59:000' as datetime) as the_dt_value union all
select 7 as id, cast('2013/07/01 23:59:59:005' as datetime) as the_dt_value union all
select 8 as id, cast('2013/07/01 23:59:59:505' as datetime) as the_dt_value union all
select 9 as id, cast('2013/07/01 23:59:59:995' as datetime) as the_dt_value
)
I'm keenly aware not everyone is going to agree with me, that's fine, but please test your assumptions about how your favorite method works.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Author
Commented:I'm comfortable the way it is. If others aren't I'm also OK with just making into a draft I can refer to at some waayyyyy distant time when I feel EE articles are something I will revisit.
Congo reference is due to some unwelcome changes in the profile, I may change it from time to time. Changed.