SQL Server datetime data type does not allow many formats

Question

Hi

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

You can style the datetime when you select it like so:

convert(varchar, [datefield], style)

Which allows you to format the date when you get it. This makes the format when storing it trivial.

Here's a list of all the styles you can use.

Answer 2

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.

Answer 3

The date isn't actually saved in a particular format; it is displayed in either the default format or the format you choose.

Where are you 'seeing' the date? Your application will probably not display the date the same as SQL Server Management Studio.

How a user formats a date so it can be saved is an issue for your application as well (As long as it sends a valid date to the server.).

Answer 4

I must admit I never tried to force the database to keep any particular date/time format. I guess it's not really necessary.

So you have a date/time field and you can sort your data by it? That's about as much you need from a DBMS. Perform the final formatting in the presentation layer of your application.