The Lone Date Ranger :: there is just one right way

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT
Published:
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.

Put bluntly: Do NOT use "between ... and" (see "Beware of Between" too)

Instead: prescribe what you what using combinations of Greater Than, Less Than and Equals

ABSOLUTELY do NOT: try to fiddle with any upper boundaries, just use Less Than - it's that simple. Do NOT subtract a minute, or a second, or a millisecond. Do NOT subtract anything from the boundary, it isn't needed. Use " less than the next time unit" (next day, next month, next year).

Right, now I've got into all sorts of twists here, especially for sql server related folks, because they have a such myriad of favourite convert, datediff, dateadd combinations and approaches to this.

Stop. Why the complexity? First principle, don't apply functions to data unless it's absolutely needed. Well, for the purposes of evaluating a date(time) field: convert isn't needed, dateadd isn't needed, datediff isn't needed. DON'T do this ON THE DATA just to decide if the data is less than some other date(time) measure.

OK, some of this off my chest.

For the curious, here are some variants of deciding if some datetime values is in June or July. Not rocket science, but please be prepared to open your mind to some interesting results. Look at the results! Here are 12 methods for deciding if a datetime value is in June or July. Only a few get this right!
 
 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)                 

Open in new window

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

Open in new window

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
                                          )

Open in new window

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.

I'm also only too well aware that the list of techniques above isn't complete - but please, I don't want to collect more methods. I have one, it works every time, reliably, without regard to the data type precision and it is both easy to read and maintain.


[expression] >= [low_boundary] and [expression] < [high_boundary]

one greater than
one equals
one less than

no gaps, no overlaps.

& no functions applied to the data either!

off my chest.
2
1,750 Views
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT

Comments (1)

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Author

Commented:
this was a blog, when we had those things, I made it into a draft article so I didn't lose it completely. I like the blog capability btw, still think it was a silly idea to remove it. It was drafted then and I really have no time for articles at the moment.

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.

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.