Finding Specific Dates in SQL Server

I gathered some useful date functions around the net. This is will be helpful in finding specific dates. Here are some of them:

declare @BaseDate datetime

set @BaseDate='20120205 21:30:27.380'

SELECT
Today=isnull(@BaseDate,GETDATE()),

DayToday=datename(dw,isnull(@BaseDate,GETDATE())),

TodayMidnight=DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0),

TodayEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,+1,isnull(@BaseDate,GETDATE()))),0)),


Yesterday=DATEADD(d,-1,isnull(@BaseDate,GETDATE())),

YesterdayMidnight=DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,-1,isnull(@BaseDate,GETDATE()))),0),

YesterdayEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),


Tomorrow=DateAdd(dd,1,isnull(@BaseDate,GETDATE())),

TomorrowMidnight=DATEADD(dd,DATEDIFF(dd,0,DateAdd(dd,1,isnull(@BaseDate,GETDATE()))),0),

TomorrowEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,+1,DateAdd(dd,1,isnull(@BaseDate,GETDATE())))),0)),


FirstDayofWeek=dateadd(dd,(datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))-1)*-1,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofWeek=dateadd(ms,-2,dateadd(dd,8-datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))),

FirstDayofLastWeek=dateadd(dd,-7,dateadd(dd,(datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))-1)*-1,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))),

LastDayofLastWeek=dateadd(dd,-7,dateadd(ms,-2,dateadd(dd,8-datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)))),


FirstDayofMonth=DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0),

LastDayofMonth=DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE()))+1,0))),

FirstDayofLastMonth=DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofLastMonth=DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0))),


LastDayLastQuarter=DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())), 0)),

FirstDayThisQuarter=DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())), 0),

LastDayThisQuarter=DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) +1, 0)),

FirstdayNextQuarter=DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) + 1, 0),

LastDayNextQuarter= DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) +2, 0)),


FirstDayofYear=DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0),

LastDayofYear=DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE()))+1,0))),

FirstDayofLastYear=DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofLastYear=DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0)))

Here's the result set:

Today                   DayToday    TodayMidnight           TodayEndofDay           
----------------------- ----------- ----------------------- ----------------------- 
2012-02-05 21:30:27.380 Sunday      2012-02-05 00:00:00.000 2012-02-05 23:59:59.997 


Yesterday               YesterdayMidnight       YesterdayEndofDay       
----------------------- ----------------------- ----------------------- 
2012-02-04 21:30:27.380 2012-02-04 00:00:00.000 2012-02-04 23:59:59.997 


Tomorrow                TomorrowMidnight         TomorrowEndofDay        
----------------------- ----------------------- ----------------------- 
2012-02-06 21:30:27.380 2012-02-06 00:00:00.000 2012-02-06 23:59:59.997 


FirstDayofWeek          LastDayofWeek           
----------------------- ----------------------- 
2012-01-30 00:00:00.000 2012-02-05 23:59:59.997 


FirstDayofLastWeek      LastDayofLastWeek       
----------------------- ----------------------- 
2012-01-23 00:00:00.000 2012-01-29 23:59:59.997 


FirstDayofMonth         LastDayofMonth          
----------------------- ----------------------- 
2012-02-01 00:00:00.000 2012-02-29 23:59:59.997 


FirstDayofLastMonth     LastDayofLastMonth      
----------------------- -----------------------
2012-01-01 00:00:00.000 2012-01-31 23:59:59.997 


FirstDayLastQuarter     LastDayLastQuarter
----------------------- -----------------------
2011-10-01 00:00:00.000 2011-12-31 00:00:00.000


FirstDayThisQuarter     LastDayThisQuarter
----------------------- -----------------------
2012-01-01 00:00:00.000 2012-03-31 00:00:00.000


FirstdayNextQuarter     LastDayNextQuarter
----------------------- -----------------------
2012-04-01 00:00:00.000 2012-06-30 00:00:00.000


FirstDayofYear          LastDayofYear           
----------------------- ----------------------- 
2012-01-01 00:00:00.000 2012-12-31 23:59:59.997 


FirstDayofLastYear      LastDayofLastYear
----------------------- -----------------------
2011-01-01 00:00:00.000 2011-12-31 23:59:59.997

Remember, the variable needs to be of DateTime datatype, not SmallDateTime.

~~ CK

Counting Weekends Between Date Range

I encountered a requirement that I need to count the number of Weekends, number of Saturdays and number of Sundays between two date range.
Here's what I got so far...

declare @startdate as datetime, @enddate as datetime

select  @startdate = '12-26-2008', @enddate = '01-26-2009'

select
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SATURDAY', 'SUNDAY') then 1
 else 0
end) as 'Number of WeekEnd',
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SUNDAY') then 1
 else 0
end) as 'Number of Sunday',
sum(  
case
 when datename(dw,dateadd(dd,days,@startdate))
      in ('SATURDAY') then 1
 else 0
end) as 'Number of Saturday'
from
(select top 365 colorder - 1 as days
 from master..syscolumns where id = -519536829
order by colorder) x
where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0

Here's the resultset:

Number of WeekEnd  Number of Sunday  Number of Saturday
-----------------  ----------------  ------------------
   10                  5                 5

Any suggestions to improve this code is always welcome...


~~CK

How to check for leap year?

Checking leap year is one of the most common checks that I perform in date values. Wikipedia have this pseudo-code to calculate for leap year:

if ((year modulo 4 is 0) and (year modulo 100 is not 0)) or
(year modulo 400 is 0) then 
   leap
else 
   not_leap

I've been using this algorithm all my programming life, and there's really nothing wrong with it. SQL Server, however, can accurately handle date data and have a couple functions that can be used to check for leap year without passing through this complicated algorithm. What I did is just to check if the expression is a valid date expression using the ISDATE() function.

Running the queries below:

select isdate('20070229') as validdate
select isdate('20080229') as validdate

will return the following result sets:

validdate
-----------
0
validdate
-----------
1

So if I have an integer year variable, I just need to build the date string.

set nocount on
declare @YearVar int

set @YearVar = 2007
select
isdate(cast(@YearVar as varchar(4)) + '0229') as validdate

set @YearVar = 2008
select
isdate(cast(@YearVar as varchar(4)) + '0229') as validdate

Here are the result sets:

validdate
-----------
0

validdate
-----------
1

To ensure that the date string will be properly interpreted, I use the unseparated date string format. For more on that, read my old notes.

Here's a sample code that illustrate how this algorithm works:

set nocount on
declare @YearVar int

set @YearVar = 2007
select
@YearVar as YearVar,
 case
when isdate(cast(@YearVar as varchar(4)) + '0229') = 1 then
     'leap year'
   else 'not leap year'
 end as validdate

YearVar     validdate
----------- -------------
2007        not leap year

    
set @YearVar = 2008
select
@YearVar as YearVar,
 case
when isdate(cast(@YearVar as varchar(4)) + '0229') = 1 then
     'leap year'
   else 'not leap year'
 end as validdate
    
YearVar     validdate
----------- -------------
2008        leap year    

~~CK

Careful EXISTS

In one of my old notes, I wrote how JOIN, IN and EXISTS are all affected by NULL values in the columns used to join tables, or to check for existence of relationship (parent-child) between tables.

Here are some queries that use EXISTS that will not give any syntax error but could give unexpected results

Consider this sample table

select * from Authors
select * from Books

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco
2           Paul                           Los Angeles
3           George                         San Diego
4           Ringo                          Oakland

aid         BookID      BookTitle                      BookPrice
----------- ----------- ------------------------------ -----------
1           1           Yesterday                      5.00
1           2           In My Life                     7.50
1           3           Hey Junde                      4.45
2           4           Fool On the Hill               NULL
2           6           If I Fell                      7.80
4           7           Let It Be                      NULL
4           8           Till There Was You             0.00
2           9           Yellow Submarine               34.65
1           10          I Should Have Known Better     65.33

Now if I execute these queries:

select * 
from authors 
where  exists 
   (select Authorid from Books  
        where BookTitle = 'Yesterday')

select * 
from authors
where exists 
   (select aid from Books 
      where BookTitle = 'Let It Be')

Here are the result sets:

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco
2           Paul                           Los Angeles
3           George                         San Diego
4           Ringo                          Oakland

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco
2           Paul                           Los Angeles
3           George                         San Diego
4           Ringo                          Oakland

The first query did not result any error. This, in spite the column AuthorID does not exist in Books table.

Separately executed, the subquery will return an error:

select Authorid from Books  
  where BookTitle = 'Yesterday'

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Authorid'.

The parser did not return any error even though AuthorID does not exists in Books. This is because it exists in Authors. In this case, the query will use the column in Authors table instead. This could return unexpected results.

To illustrate this query in another way:

declare @myVar int

set @myVar = 1

select @myVar myVar from Books where BookTitle = 'Yesterday'

set @myVar = 2

select @myVar myVar  from Books where BookTitle = 'Yesterday'

Here are the result sets:

myVar
-----------
1

myVar
-----------
2

I also noticed that the queries returned all rows. This is due to the nature of how EXISTS works. It checks if the subquery clause returns at least a single row. As illustrated in another way, the above query always returns a valid row regardless of the value of @myVar. The query itself is incomplete. The filter condition that should link the two table is missing. If I am to use EXISTS to relate the two tables, the query should be written this way:

select * 
from authors a
where  exists 
   (select 1 from Books b 
        where a.AuthorID = b.aid and   BookTitle = 'Yesterday')

Here’s the result set:

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco

~~CK

Optimizing Your WHERE Clause

The WHERE clause is one of the most commonly used optional part of a query. Simply put, it filters out rows and narrows the number of rows returned by a query based on the condition included in the clause.

It's a common misconception that the SQL optimizer will always use an index whenever the table has a useful one. This is not always the case. In some cases, index will not be use and a table/index scan will be performed resulting in slow processing.

It's also widely accepted that the arrangement of expressions and the operator used does not matter, since the optimizer will parse and prepare an execution plan for the query anyway. Although this is true most of the time, arranging the logical expression properly can improve processing.

Here are some consideration that I keep in mind whenever I build my WHERE clause.

Avoid using expression that has a function on a column. This will prevent the optimizer from using the index and instead perform a table/index scan.

This query will not use an index:

select *
from AdventureWorksDW..FactInternetSalesReason
WHERE substring(SalesOrderNumber,1,2) = 'SI'

Modified, this one will use an index:

select *
from AdventureWorksDW..FactInternetSalesReason
WHERE SalesOrderNumber like 'SI%'

If the use of a function can not be avoided, use an indexed computed column instead.

If the SQL Server is not configured to be case-sensitive, do not bother using LOWER() or UPPER() functions .

These are three identical queries that will return identical results:

select * 
from AdventureWorksDW..DimGeography 
WHERE CountryRegionCode = 'AU'

select * 
from AdventureWorksDW..DimGeography 
WHERE upper(CountryRegionCode) = 'au'

select * 
from AdventureWorksDW..DimGeography 
WHERE CountryRegionCode = 'au'

The second query, however, will not use an index.

Use the equal (=) operator to compare two strings instead of LIKE.

These two queries will return the same results:

select * from AdventureWorksDW..FactInternetSales
WHERE salesordernumber = 'SO43703'

select * from AdventureWorksDW..FactInternetSales
WHERE  salesordernumber LIKE 'SO43703'

The first query is more efficient than the second one. If the LIKE operator can not be avoided, use as much leading character as much as possible. If the application performs too many LIKE operation, consider SQL Server's full-text search option instead.

select * from AdventureWorksDW..FactInternetSalesReason
WHERE salesordernumber like 'S%1'

The above query will perform faster than the query below:

select * from AdventureWorksDW..FactInternetSalesReason
WHERE salesordernumber like '%1'

Although they are not identical, given the choice, use the former rather than the latter.

Here are the most common operators in WHERE clause arranged based on best performing first:

=
>, <, >=, <= 
LIKE 
<>, NOT

Avoid using NOT operator as much as possible. Although not always the case, WHERE clause that uses the NOT operator does not utilize index.

select * from AdventureWorksDW..FactInternetSales
WHERE not ShipDateKey >= 10

will perform faster as:

select * from AdventureWorksDW..FactInternetSales
where ShipDateKey <10

Given the choice, use EXISTS() instead of IN(). Moreover, IN() have some issues handling with NULL values.

Force the optimizer to utilize an index by using index hint on the query. Use this as the last resort for optimization.

Given the choice, use BETWEEN instead of IN. The BETWEEN operator performs faster than IN.

If the clause have multiple logical expression connected by two or more AND operators, locate the expression that will be LEAST likely to be true. This way, if it's false, the clause will immediately end. If both expressions are equally likely to be false, test the least complex expression first. This way, if it's false, the more complex one need not be tested. Also, consider creating an index for a selective column or a covering index for the query.

If the clause have multiple logical expression connected by two or more OR operators, locate the expression that will be MOST likely to be true. This way, if it's true, the clause will immediately end. If both expressions are likely to be true, test the least complex expression first. This way, if it's true, the more complex one need not be tested.

Remember that IN operator is actually another form of OR, so place the most probable value at the start of the list.

A query will perform a table/index scan if it contains OR operator and if any of the referenced column does not have a useful index.

This query will perform a table/index scan. For this query to utilize an index, there must be an index on all three columns in the clause. Even if two out of the four columns have an index, it will still perform a table/index scan.

select *
from AdventureWorksDW..FactInternetSales
where ShipDateKey = 3
or PromotionKey = 3
or SalesTerritoryKey = 5
or SalesOrderLIneNumber = 3

If creating an index for each of these columns is not an option, rewrite the query to use UNION ALL (not UNION) instead. This way the query with useful index will be utilize, even if it's just the one or two out of the four query. It will still execute more efficiently.

select *
from AdventureWorksDW..FactInternetSales
where ShipDateKey = 3
union all
select *
from AdventureWorksDW..FactInternetSales
where PromotionKey = 3
union all
select *
from AdventureWorksDW..FactInternetSales
where SalesTerritoryKey = 5
union all
select *
from AdventureWorksDW..FactInternetSales
where SalesOrderLIneNumber = 3

The above query will give the same results but will run faster than using multiple ORs. If ShipDateKey and PromotionKey have useful index, it will use their respective index for that part of the query, improving the speed of the entire query.

Given the option, use EXIST or a LEFT JOIN instead of IN to test/compare the relationship of a parent-child tables.

Beware of redundant clause.

This query have a redundant WHERE clause:

select DueDateKey, ShipDateKey,
PromotionKey, CustomerKey
from AdventureWorksDW..FactInternetSales
WHERE
DueDateKey = 8 and ShipDateKey = 8 and
PromotionKey = 1 or ShipDateKey = 8 and PromotionKey = 1

ShipDateKey = 8 and PromotionKey = 1 is a subset of DueDateKey = 8 and ShipDateKey = 8 and PromotionKey = 1. What the optimizer will do is it will return all the rows requested.

Here's the result set:

DueDateKey  ShipDateKey PromotionKey CustomerKey
----------- ----------- ------------ -----------
13          8           1            11003
13          8           1            14501
13          8           1            21768
13          8           1            25863
13          8           1            28389

It may look like the query returned the requested rows efficiently. In reality what happen is, it returned these rows twice then perform a DISTINCT to remove the redundant rows.

Convert frequently running batch to stored procedure, specially, if the queries in the batch use user-defined variables. The optimizer might not take advantage of a useful index to run the queries in the batch. This is because the optimizer does not know the value of these variables when it chooses a way to access the data. If the batch is not frequently executed, consider including an INDEX hit on the query.

As much as possible, include a WHERE clause to the query. This will improve the way SQL Server retrieve the data.



~~CK

How do SQL Server Programmers Search for Their Dates?

Being a user of SQL Serever, I dealt with searching and using dates in logical conditions, in one way or another. Initially, it was quite confusing.

Here are a couple of factors.

First, it's because, as of SQL 2005, there are no data types that holds date only, nor time only data. SQL Server stores both the date part and time part using the same data type. The only difference is the precision of these data types.

Second is because you have to use string literals to handle static date data. Users usually miss the time part of the string literal. SQL Server will default the converted time part to zero (00:00:00.000). This could lead to logical conditions to return unexpected results. (For more on all these, read my old notes).

Try and simulate some logical conditions. Consider this sample table:

declare @myTable table(rownum int, DateData datetime)
set nocount on

insert into @mytable values(1,'2005-10-14 01:36:25.440')
insert into @mytable values(2,'2005-10-14 01:36:25.497')
insert into @mytable values(3,'2005-10-14 01:36:25.570')
insert into @mytable values(4,'2005-10-14 01:36:25.627')
insert into @mytable values(5,'2005-10-14 01:36:25.683')
insert into @mytable values(6,'2005-10-14 01:36:25.740')
insert into @mytable values(7,'2005-10-15 00:00:00.000')
insert into @mytable values(8,'2008-07-24 12:52:42.360')
insert into @mytable values(9,'2008-07-25 00:00:00.000')
insert into @mytable values(10,'2008-07-25 12:38:35.060')
insert into @mytable values(11,'2008-07-25 12:38:35.137')
insert into @mytable values(12,'2008-07-26 00:00:00.000')
insert into @mytable values(13,'2008-08-13 00:00:00.000')

select * from @myTable

rownum      datedata
----------- -----------------------
1           2005-10-14 01:36:25.440
2           2005-10-14 01:36:25.497
3           2005-10-14 01:36:25.570
4           2005-10-14 01:36:25.627
5           2005-10-14 01:36:25.683
6           2005-10-14 01:36:25.740
7           2005-10-15 00:00:00.000
8           2008-07-24 12:52:42.360
9           2008-07-25 00:00:00.000
10          2008-07-25 12:38:35.060
11          2008-07-25 12:38:35.137
12          2008-07-26 00:00:00.000
13          2008-08-13 00:00:00.000

Now search for specific records using the date data as filter condition :

select *
from @myTable
where DateData = '20080724'

select *
from @myTable
where DateData = '20080813'

Here are the result sets:

rownum      datedata
----------- -----------------------

rownum      datedata
----------- -----------------------
13          2008-08-13 00:00:00.000

Now, why did the first query did not return any rows while the second query returned the 13th row? That's in spite of row 8 being the only one with date July 24, 2008 anyway.

A more detail analysis shows that the WHERE clause of the query compares the DateData column to a date-formatted string. In order to compare two values, they must be of the same data type, otherwise, the data type with the lower precedence will be converted to the data type with the higher precedence. In this case, datetime is higher than any other native string data types. The first query is synonymous to:

select * from @myTable
where datedata = cast('20080724' as datetime)

rownum      datedata
----------- -----------------------

Since the function converted a date-literal with no time part, it sets the time to zero (00:00:00.000). Resulting to a false condition (2008-07-24 12:53:00 <> 2008-07-24 00:00:00.000).

So how do SQL Server Programmers search for their dates? Here are some options how to retrieve the 8th record.

It's always possible tp convert the date column into string with no time part and compare two strings. Something like:

select * from @myTable
where convert(varchar(20), datedata, 112) = '20080724'

rownum      datedata
----------- -----------------------
8           2008-07-24 12:52:42.360

It did. Technically, there's nothing wrong with this code. It returned the desired result. The only problem that it will encounter later is performance. SQL Server will not use index to optimize our query since we use a function in date column for our WHERE clause. This is OK if the query is processing a small table. If it's reading a large volume of data, this will tremendously affect the execution time.

Another option is to grab all records with dates between 20080724 AND 20080725.

select * from @myTable
where datedata BETWEEN '20080724' and '20080725'

rownum      datedata
----------- -----------------------
8           2008-07-24 12:52:42.360
9           2008-07-25 00:00:00.000

Now why did it include row 9? It's the way how BETWEEN operator works. BETWEEN is a logical operator that operates inclusive-ly. The above query is similar to:

select * from @myTable
where datedata >= '20080724' and datedata <= '20080725'  

The second condition will follow the “explicit conversion of lower precedent data type with no time part” rule, so the 9th row was included to the result set. So, try and include the time part:

select * from @myTable 
where datedata
BETWEEN '20080724 00:00:00.000' and '20080724 23:59:59.999'

rownum      datedata 
----------- ----------------------- 
8           2008-07-24 12:52:42.360 
9           2008-07-25 00:00:00.000  

Why did it still return 2 rows? The conversion, as it is, has nothing to do with this. The second expression ('20080724 23:59:59.999') will be properly converted to a DateTime expression, complete with time part. However, the DateTime precision is 3.333 seconds (For more about DateTime and SmallDatetime rounding and precision, read my old notes) resulting the second part to be rounded to '2008-07-25 00:00:00.000'. To resolve this rounding issue, try to prevent the time part from rounding up by doing:

select * from @myTable
where datedata
BETWEEN '20080724 00:00:00.000' and '20080724 23:59:59.998'  

rownum      datedata 
----------- ----------------------- 
8           2008-07-24 12:52:42.360  

And if it's SmallDateTime, the code should be :

select * from @myTable 
where datedata
BETWEEN '20080724 00:00:00.000' and '20080724 23:59:00'  

rownum      datedata 
----------- ----------------------- 
8           2008-07-24 12:53:00  

Now there are two ways of doing it depending on which data types the query is dealing with. Although, these are all valid codes, it is not recommended to have two versions of codes to handle these two sibling data types. Also, it's necessary to be always conscious of whether it's dealing with a DateTime or a SmallDateTime expression. To be more flexible, it would be better to create something like this:

select * from @myTable 
where datedata >='20080724 ' and datedata < '20080725' 

rownum      datedata 
----------- ----------------------- 
8           2008-07-24 12:53:00  

The above code is clearer and SQL Server can also use index for query optimization. For range of dates, it can easily be extended and modify the condition of the same code:

select * from @myTable 
where datedata >='20080724 ' and datedata < '20080727'

rownum      datedata
----------- -----------------------
8           2008-07-24 12:53:00
9           2008-07-25 00:00:00
10          2008-07-25 12:39:00
11          2008-07-25 12:39:00
12          2008-07-26 00:00:00

As a general rule, use a method that can handle both DateTime and SmallDateTime, specially if you the requirement does need to deal with the time part. Also, consider the performance of the query. Placing a date data type column in a function as part of a filter condition will ignore the index and will perform a table scan. Most importantly, if using a function is unavoidable, be aware that most date and time functions are dependent to the user's default language, SET LANGUAGE and SET DATEFORMAT settings.



~~CK

The Basics of Dates

On my old notes, I wrote down some of the similarities and differences between smalldatetime and datetime data types. Now I'll write up some more of the basics that must be considered before using these data types.
Default Values
Both these data types use the base date (January 1, 1900) as their default value. Even if Datetime can handle much earlier dates, it will always default to the base date.

select cast('' as smalldatetime) as 'smalldatetime',
cast('' as datetime) as 'datetime'

Here is the result set:

smalldatetime           datetime
----------------------- -----------------------
1900-01-01 00:00:00     1900-01-01 00:00:00.000

Both function returned the base date as the default date.

Time, however, will always default to zero (00:00:00). The only difference is the fractional seconds that DateTime can handle.

select
cast('12/29/2008' as smalldatetime) as 'smalldatetime',
cast('12/29/2008' as datetime) as 'datetime'

smalldatetime            datetime
----------------------- -----------------------
2008-12-29 00:00:00     2008-12-29 00:00:00.000

Rounding and Precisions
SmallDateTime has a simple way of preserving its one minute precision. All dates with 29.998 seconds and below will be rounded down to the nearest minute, all dates with 29.999 seconds and higher will be rounded up to the next minute.

select '20091229 23:59:29.998' as 'string',
cast('20091229 23:59:29.998' as smalldatetime) as 'smalldatetime'

string                smalldatetime
--------------------- -----------------------
20091229 23:59:29.998 2009-12-29 23:59:00


select '20091229 23:59:29.999' as 'string',
cast('20091229 23:59:29.999' as smalldatetime) as 'smalldatetime'

string                smalldatetime
--------------------- -----------------------
20091229 23:59:29.999 2009-12-30 00:00:00

DateTime has a more complicated rounding off calculation. Dates are rounded to increments of .000, .003 or .007 seconds.
Consider the following examples:
declare @DateData table(stringdate varchar(25))

set nocount on
insert into @DateData values('20091229 23:59:29.990')
insert into @DateData values('20091229 23:59:29.991')
insert into @DateData values('20091229 23:59:29.992')
insert into @DateData values('20091229 23:59:29.993')
insert into @DateData values('20091229 23:59:29.994')
insert into @DateData values('20091229 23:59:29.995')
insert into @DateData values('20091229 23:59:29.996')
insert into @DateData values('20091229 23:59:29.997')
insert into @DateData values('20091229 23:59:29.998')
insert into @DateData values('20091229 23:59:29.999')

select stringdate, cast(stringdate as datetime) converted
from @DateData

Here's the result set:

stringdate                converted
------------------------- -----------------------
20091229 23:59:29.990     2009-12-29 23:59:29.990
20091229 23:59:29.991     2009-12-29 23:59:29.990
20091229 23:59:29.992     2009-12-29 23:59:29.993
20091229 23:59:29.993     2009-12-29 23:59:29.993
20091229 23:59:29.994     2009-12-29 23:59:29.993
20091229 23:59:29.995     2009-12-29 23:59:29.997
20091229 23:59:29.996     2009-12-29 23:59:29.997
20091229 23:59:29.997     2009-12-29 23:59:29.997
20091229 23:59:29.998     2009-12-29 23:59:29.997
20091229 23:59:29.999     2009-12-29 23:59:30.000

Notice that .995 is rounded to .997 even if it's exactly between .003 and .007. This is because seconds are still integers and will still follow the integer rule of rounding off.

Formats

Whenever a date values is used in T-SQL, it’s probably specified in string literal. However, SQL Server might incorrectly interpret these strings as different dates. A date value '08/12/05' could be interpreted in six different ways. These interpretations are usually affected by the SET DATEFORMAT and SET LANGUAGE settings. There are some string literals that are not affected by these settings. Unless you are sure of these settings, try using a setting-independent format.

Here are some of the acceptable date literal formats:

Numeric Separated	'12/29/2008 14:21:00.000'	DF	LN
Numeric Unseparated	'20081229 14:21:00.000'	-	-
ANSI-SQL	'1998-12-23 14:23:05'	DF	LN
Alphabetic	'29 December 1998 14:23:05'	-	LN
ODBC DateTime	{ts '1998-12-29 14:23:05'}	-	-
ODBC Date	{d '1998-12-29'	-	-
ODBC Time	{t '14:29:09'}	-	-
ISO 8601	'1998-12-29T14:27:09'	-	-
Time	'14:29:09' '2:29:09 PM'	-	-

DF - SET DATEFORMAT dependent
LN - SET LANGUAGE dependent

Notice that ANSI-SQL is also a numeric-separated format, but still LANGUAGE and DATEFORMAT dependent. ODBC uses escape sequences to identify date (d), time (t) and timestamp (date+time). SQL Server always treats ODBC date data as DateTime.

Let's see how DATEFORMAT and LANGUAGE settings can affect your literal string. The code below will give an error:

set dateformat dmy
select cast('12/29/2008' as datetime)

Here's the result:

-----------------------
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

This code however, is valid:

set dateformat mdy
select cast('12/29/2008' as datetime)

Here's the result:

-----------------------
2008-12-29 00:00:00.000

Here's how language setting can affect your date literals:

set language British
select datename(month,cast('08/12/05' as datetime))

Here's the result:

Changed language setting to British.

------------------------------
December

And changing the setting :

set language us_english
select datename(month,cast('08/12/05' as datetime))

Can change the result of the same code to:

Changed language setting to us_english.
------------------------------
August

To avoid these errors, try to use those formats that are not dependent to LANGUAGE and DATEFORMAT settings.



~~CK

How SQL Programmers Choose their Dates

(I just created this blog and been thinking where to start. I decided to start with one of the basic data types. The, sometimes confusing, Datetime and its younger sibling, SmallDateTime Data Types. I hope this notes will be able to help you understand the similarities and differences between these two data types)

As of SQL 2005, there are only two data types that handles both date and time data: DateTime and SmallDateTime. There was no type that handles neither date only, nor time only. Both data types handles data with a date part combined with a time of the day.

Instead of repeating what has been posted on articles around the net and Book Onlines, I think it would be better to just give the highlights.

SmallDateTime	DateTime
Minimum Value
January 1, 1900 00:00:00	January 1, 1753 00:00:00.000
Maximum Value
June 6, 2079 59:59:00	December 31, 9999 59:59:59.997
Precision
Up to 3.333 millisenconds	Up to one minute
Storage Size
8 bytes (Two 4-byte int)	4 bytes (Two 2-byte int)
Accuracy
Rounds to increments of .000, .003, .007	Up to 29.998 seconds are rounded down, all else round up
Default Value (base date)
January 1, 1900 00:00:00	January 1, 1900 00:00:00

It's a common misconception that SQL Server stores these data type in some sort of date-structured formats. Internally, it's being stored as a two-part integer. DateTime, being able to handle higher precision, stores data in a two 4-byte integer. The first four stores the number of days before or after the base date. The other four stores the number of milliseconds since midnight. SmallDateTime stores data as a two 2-byte integer. The first two stores the number of days after the base date, the other two stores the number of minutes since midnight.


How to choose?

The answer would depend on the range of value that will be processed. Based on the table above, SmallDateTime can only handle dates from January 1, 1900 through June 6, 2079. If the expected date value is outside of this range, use DateTime.

Some application would only require up to the minute accuracy. For example, most payroll applications calculate number of hours worked up to the minute only, and does not bother considering seconds and milliseconds. In this case, use SmallDateTime. On the other hand, some telecommunication company uses the actual Elapsed Conversation Duration (ECD), that is accurate to the seconds, and sometimes up to milliseconds ,to come up with various calculations of the billable minutes. Some calculations consider a second as a billable unit, others use pulse (6 seconds = 1 pulse). Depending on the application (consider future requirements as well) these sample cases could easily be the major consideration in deciding which type to use.

Once a date data is stored to a SmallDateTime column, the second precision will be gone even if the data type is modified to DateTime later. Only those newly inserted or newly updated values will be affected.



~~CK