This post follows my previous three posts


Part I

Part II

Part III

Best practices when using datetime values

1 Always use proper DATETIME datatype to store dates

This is very important becuase lot of people use varchar datatype to store formatted date values.
Only if you use datetime datatype, you will be able to use all date related functions over it

Consider the following example

declare @emp_details table(emp_id int primary key, join_date varchar(30))
insert into @emp_details (emp_id,join_date)

select 1,'Jul 16, 2001' union all
select 2,'Feb 10, 2001' union all
select 3,'Jan 12, 1999' union all
select 4,'Dec 15, 1995' union all
select 5,'Sep 10, 2000' union all
select 6,'Oct 15, 1996'

Note that join_date is of varchar datatype Now we will see what happens when we try the following

select * from @emp_details order by join_date

Result

emp_id      join_date
----------- ------------------------------
4           Dec 15, 1995
2           Feb 10, 2001
3           Jan 12, 1999
1           Jul 16, 2001
6           Oct 15, 1996
5           Sep 10, 2000

The results are actually not sorted as date but sorted as varchar

select * from @emp_details 
where join_date<='Jan 12, 1999'

Result

emp_id      join_date
----------- ------------------------------
2           Feb 10, 2001
3           Jan 12, 1999
4           Dec 15, 1995

As you see it returns wrong data

So based on the above examples it is very clear that you should always use proper DATETIME datatype to store dates

There is also a possibility that the column can have invalid dates
Using DATETIME column will automatically avoid this

2 Do date formation at your front end application

Lot of people,who use DATETIME datatype to store dates, are trying to format dates in sql and send them to front end application.
Whenever you format date values using CONVERT functions, all your dates become varchars and you can't do all kinds of date
calculations,functions,etc as you have seen in the point 1.

You need to again convert them back to datetime in order to use date functions
So by formatting dates, you are unneccessarily converting dates to varchars and varchars to datetime.
All your front end applications support FORMAT function which you can make use of it

Also some people use convert function in the WHERE clause CONVERTing DATETIME to VARCHARs
If the column is indexed, usage of convert function will prevent it thus resulting to a scan.
So this is one more reason why you should not convert the dates

3 Beware of ISDATE() function that it is not fully reliable

I have already blog about this. Read my post about Handle ISDATE() with care

4 Proper Usage of CONVERT function over DATETIME column

There may be some cases where you can't avoid formatting the dates using sql
They include
1 Export results to text file with specific date format
2 Import data from other sources where dates are in different formats
3 Front end application can't be changed but it needs specific date formats for display
etc

You should use CONVERT function to format the dates only if you have any cases specified above.
Otherwise dont use CONVERT Function