T-SQL’s way of “Happy Holidays”

The following code may not be understandable but the output will surprise you. In SSMS, set the result mode to Text (Press CTRL+T) and then execute the following code

set nocount on select space(17-len(replicate(char(58),no)))+ replicate(char(59),no*2-1)from (select top 10 row_number() over (order by (select 1)) as no from (select 0 as no union all select 0 union all select 0) as t1 cross join (select 0 as no union all select 0 union all select 0) as t2) as t union all select space(14)+replicate(char(124),5) union all select space(10)+cast(0x486170707920486F6C6964617973 as varchar(100))

Advertisements

Be careful when using between operator on DATETIME comparison

If you want to check for the ranges in the DATE values you can use BETWEEN operator. But you need to be careful to use BETWEEN operator when dealing with DATETIME values.

 

Consider the following data

create table #t(date_col datetime)
insert into #t (date_col)
select '2001-01-01T12:14:20' union all
select '2001-01-17T19:20:55' union all
select '2001-01-21T00:00:00' union all
select '2001-01-31T16:22:56' union all
select '2001-02-01T00:00:00'

Suppose you want to find out the date values of Jan 2001, you can use below query

select date_col from #t
where date_col between '20010101' and '20010131'

which results to

date_col
-----------------------
2001-01-01 12:14:20.000
2001-01-17 19:20:55.000
2001-01-21 00:00:00.000

If you see the value  2001-01-31 16:22:56.000 is missing because it has time part and in the query it is not specified.

Ok. Let us include time part in the query. As we do not know what maximum value it may have, let us use the maximum time

select date_col from #t
where date_col between '20010101' and '20010131 23:59:59:999'

The result is

date_col
-----------------------
2001-01-01 12:14:20.000
2001-01-17 19:20:55.000
2001-01-21 00:00:00.000
2001-01-31 16:22:56.000
2001-02-01 00:00:00.000

As you see you have an extra value for Feb 2001 which is not needed. The problem is that because of millisecond rounding 20010131 23:59:59:999 becomes 20010201 00:00:00.000. This is expected for older datetime columns and the maximum time value as it may differ from datatype to datatype (for smalldatetime and datetime it is rounded and for datetime2(7) it is not rounded)

Another method is to use >= and < operators as shown below

select date_col from #t
where date_col >=‘20010101’ and date_col<‘20010201’


date_col
-----------------------
2001-01-01 12:14:20.000
2001-01-17 19:20:55.000
2001-01-21 00:00:00.000
2001-01-31 16:22:56.000

 
Which gives you the required data.

The point is that when you use BETWEEN operator both the ranges are included in the filtering and if the data has time part you need to specify it too. The maximum time part differs from datatype to datatype(smalldatetime, datetime, datetime2) and may lead to rounding of value which will get you some extra data.

So in order to avoid this I prefer to make use of >= and < combination when searching for datetime ranges as shown in the last example.
where date_col >='20010101' and date_col<'20010201'

The above means “Get me all data where date_col is greater than or equal to starting time of Jan 01, 2001 and lesser than starting time of Feb 01, 2001” which gives you all data for Jan 01, 2001. This way you do not need to worry about maximum time part of the data and do not need to specify it in the where clause.