SQL Server 2005与ASP.net日期时间格式混淆

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.

我在堆栈溢出时发现了一个类似的问题,但它并没有真正回答我的问题。我需要确保我的asp.net应用程序格式化日期dd / mm / yyyy与我的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.

如何验证服务器的日期文化(如果这就是它的名称)与我编写应用程序的方式相符?是否有特定的数据库设置和OS设置?它是特定于表格的吗?我不想改变我的日子和月份。

thank you

5 个解决方案

#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.

当您从数据库中获取DateTime时,它应该采用非文化格式(如DateTime对象,基于特定日期以来的滴答数)。只有当您将该值转换为需要关注文化的字符串时。在这些情况下,您可以使用yourDateTimeValue.ToString(“dd / MM / yyyy”,CultureInfo.InvariantCulture)来确保信息正确显示。

#2

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! :)

我相信如果您使用SqlParameters,ADO.NET将负责其余部分,您不必担心它。此外,它也有助于抵御SQL注入攻击! :)

#3

** 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. **

**注意因为SQL DateTime列是不可为空的,它们的最小值是1/1/1753而.net DateTimes是不可为空的,最小值为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.

如果您从实际的DateTime列中提取数据,则默认情况下它将始终采用相同的标准格式。要将数据保存到列,您可能需要在参数中指定SqlDbType.DateTime。

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

我从http://bytes.com/forum/thread767920.html中删除了这个:

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

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

#4

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.

只要您在应用程序中保留日期强类型(DateTime变量)并通过DBParameter / SqlParameter通过预准备语句发送日期,您的数据库将按原样使用它们。

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)

(代码是丑陋的,但希望它得到了重点)

#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.

正如其他人所提到的,就文化存储日期而言,你应该没问题。我建议您将所有时间存储为标准UTC时间。在SQL Server 2005及更早版本中,无法存储时区信息,但如果所有内容都以通用时间存储,则应该没问题,因为以后可以将时间转换为本地时间。

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.

SQL Server 2008确实有一些了解时区的数据类型,如果您使用的是.NET 3.5,则可以使用工具来协助时区处理/转换。

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

绝对保持通用格式的时间。如果您必须在多个时区工作,这将使世界变得与众不同。

#1

#2