Working with DateTime data types in SQL Server, guidelines and explanations!
Just a bit of basics about DateTime datatype in SQL Server, it has two different date/time columns. They are DATETIME and SMALLDATETIME and the difference between these two SQL Server date/time data types. SQL Server has a number of default formats it expects raw date and time data to be in when inserting dates into a DATETIME, or SMALLDATETIME columns. If you attempt to insert a date that does not match one of the default date formats, then SQL Server will reject the date/time value. If your raw data does match one of the default formats, SQL Server will automatically convert your raw data into a DATETIME value without any special consideration.
Sometimes I get confused with how to deal with various types of formats when you have the data which is globalized in terms of users usability. So here I would like to give you a lead and links on various datetime data types within SQL Server:
Demystifying the SQL Server DATETIME Datatype (one of my favourite)
Advanced SQL Server DATE and DATETIME Handling
Date and Time Manipulation in SQL Server 2000
The ultimate guide to the datetime datatypes