Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Understanding Datetime column - Part IV

Jun 29 2010 12:00AM by Madhivanan   

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


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'


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

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

Tags: t-sql, sql_server, datetime, sqlserver, BRH, DataTypes, #TSQL,

2 · 40% · 13039


Your Comment

Sign Up or Login to post a comment.

"Understanding Datetime column - Part IV " rated 5 out of 5 by 2 readers
Understanding Datetime column - Part IV , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]