SQL Server datetime data type does not allow many formats

Question

I have a table in SQL Server 2005 which has a date time field. I want to save the date time value in mm/dd/yyyy hh:mm:ss format, but I guess SQL Server allows date time in yyyy-mm-dd HH:mm:ss.ll format.

I can save date time value in mm/dd/yyyy hh:mm:ss format as a varchar, but that defeats my intention of sorting the table on that date time field.

Is there something missing in my approach? Any suggestions?

cheers

Answer 1

The datetime type does not store it in yyyy-mm-dd HH:mm:ss.ll, as you claim.

Datetime columns are stored as 8-byte binary data. The yyyy-mm-dd HH:mm:ss.ll format is just how it's returned in your query. To display it in other formats, look at the CONVERT function.