如何将日期格式更新为另一种？ [英] How to update a date format to another?

问题描述

我有一个表格列Plaerdob女巫有差异日期类型，如

1- 1-jan-1987。

2 - 1987年1月1日

3- 01-jan-1987。



这里我要更新我所有的日期格式2和3成1.



可以吗？如果是的话怎么样？



请帮忙。



谢谢

Hi,
I have a table column Plaerdob witch have diff date type like
1- 1-jan-1987.
2- 01/01/1987
3- 01-jan-1987.

Here i want to update all my dates format of 2 and 3 into 1.

Can it be possible? if yes then how?

Pls help.

Thanks

推荐答案

简单：不要将日期信息存储为字符串 - 总是将其存储为DATE或DATETIME值。

问题是有很多方法可以输入一个约会，它们都是等效的。

不幸的是，你不能仅仅通过查看字符串的日期来判断：

Simple: don't store date information as strings - always store it as DATE or DATETIME values instead.
The problem is that there are a huge number of ways to enter a date, and they are all equaly valid.
Unfortunately, you can't tell just by looking at the string which date it is:

01/02/03  Could be 1st Feb 2003  (Europe)
          Could be 2nd Jan 2003  (US)
          Could be 3rd Feb 2001  (Japan)

所以到时候字符串已到达您的数据库，为时已晚 - 您需要计算日期所需的所有信息已被丢弃：文化最有可能定义您输入的用户所设置的PC。

你应该用1-avr-1987（法语）做什么？或者1-ian-1987（德语）？



您可以通过将整个数据库读入C＃并使用DateTime.TryParse转换它们来转换它们到DateTime值，然后更新一个新列，但即使这样，上面的例子都将被假定为你运行的PC的文化。



所以答案是：是的，但它不可靠，而且几乎肯定会包含错误。



将数据库更改为使用基于日期的字段 - 从不再次将它们存储为字符串。

So by the time the string has arrived at your database, it's too late - all the information you need to work out what the date is has been discarded: it was most likely to be defined by the Culture that the PC the user you entered it was set to.
And what are you supposed to do with "1-avr-1987" (French)? Or "1-ian-1987" (German)?

You may be able to convert it by reading your whole DB into C# and using DateTime.TryParse to convert them to DateTime values and then updating a new column, but even then the examples above will all be assumed to be in the culture of the PC you app is running on.

So the answer is: Yes, but it won't be reliable, and it will almost certainly contain errors.

Change your DB to use Date based fields - and never store them as strings again.

SELECT CONVERT(VARCHAR, GETDATE(), 106) -- 04 May 2013
SELECT CONVERT(DATETIME, '04 May 2013', 106) -- 2013-05-04 00:00:00.000

我们有一篇很棒的文章。请阅读有助于转换日期和时间的SQL Server函数字符串文字和其他日期和时间格式的值。 [ ^ ]

hi OriginalGriff，

我知道这些东西但是它已经完成，有6000多个配置文件，我不知道这些列使用了多少次。

所以我想不能改变现在的这种类型，只需要考虑替代。



谢谢

hi OriginalGriff,
I know these things but it already done and there are 6000+ profiles and i dont know how many times these column used.
So i cant think to change this type as of now, just have to think about the alternate.

thanks

这篇关于如何将日期格式更新为另一种？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！