SQL Server 2005 vs. ASP.net datetime format confusion

Question

Hi,

I've found a similar question on stack overflow, but it didn't really answer the question I have. I need to make sure that my asp.net application is formatting the date dd/mm/yyyy the same as my SQL Server 2005.

How do I verify the date culture (if that's what it's called) of the server matches how I've programmed my app? Are there specific database settings and OS settings? Is it table-specific? I don't want to transpose my days and months.

thank you

Answer 1

When you get a DateTime out of the database, it should be in a non-cultured format (like the DateTime object, based on the number of ticks since a certain date). It is only when you are converting that value into a string that you need to be concerned with culture. In those cases, you can use yourDateTimeValue.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture) to make sure that the information displays correctly.

Answer 2

** Watch out because SQL DateTime columns are non-nullable and their minimum value is 1/1/1753 while .net DateTimes are non-nullable with min values of 1/1/0001. **

If you're pulling data from a real DateTime column, by default it will always be in the same standard format. For saving the data to the column, you might want to specify the SqlDbType.DateTime in your parameter.

i ripped this off of http://bytes.com/forum/thread767920.html :

com.Parameters.Add("@adate", SqlDbType.DateTime).Value = DateTime.Now;

Answer 3

Well, if you keep datetime fields in the DB you shouldn't worry about it.

As long as you keep the dates in app strongly typed (DateTime variables) and send the dates through prepared statements with DBParameter/SqlParameter your DB will take them as is.

If you use strings to hold your dates in code, some casts will ensure you send the right values:

string sqlCmd = @"SELECT *
   FROM MyTable
   WHERE MyDateField = CONVERT(datetime, '{0}', 101)";

// assuming myDateString is a string with a date in the local format
sqlCmd = string.Format(sqlCmd,
    Convert.ToDateTime(myDateString).ToString("yyyyMMdd"));

(the code is ugly, but hopefully it gets the point across)

Answer 4

I belive that if you use SqlParameters ADO.NET will take care of the rest and you don't have to worry about it. Besides, it's good for defending against SQL Injection attacks too! :)

Answer 5

As others have mentioned, you should be OK as far as storing datetimes culturally. What I would recommend is that you store all of your times as standard UTC time. In SQL Server 2005 and older there is no way to store time zone information, but if everything is stored in universal time, you should be OK because the time can be converted to the local time later on.

SQL Server 2008 does have some datatypes that are aware of time zones, and if you're using .NET 3.5 there are tools to assist with time zone handling/conversions.

Definitely keep times in universal format. This will make a world of a difference if you have to work in multiple time zones.