I must admit I dislike the SQL datetime data type – it’s long, ugly and not always readable. So I totally understand why some people will go a distance to make this type easier for them (to read, that is). Well, the good news is there is a way to convert datetime format to other data type for readability. The bad news is that once you convert it (usually to a varchar/string), the function is kinda lost. You can’t really calculate anything with a string, can you??
One may ask, can’t datetime format be formatted (ie still a datetime but with a different look)? Uhm, last time I checked, you can’t do this in SQL 2000/2005. But be assured that your plea is heard by Microsoft (sort of) as they include DATE datatype, which means you can only see the date without the time bit (yay!)
In case you’re still curious how to change the datetime to varchar, here it is:
CONVERT (varchar(10), DateColumn,103) from TableName
This will change the date format to dd/mm/yyyy. For US format, use 101 instead of 103.
For further reading, see Jeff Smith’s excellent post on this topic.
