sql - 将美国日期和时间格式的字符串转换为英国格式

Question

我有一个将日期和时间存储在SQL Server 2008表的字符串字段中的应用程序。

该应用程序会根据正在运行的PC的区域设置存储日期和时间，我们无法更改此行为。

问题在于，某些PC必须采用英国时间格式的12h时间(例如22/10/2011 1:22:35 pm)，而某些PC必须采用英国时间格式的24h时间(例如22/10/2011 13:22)。 :25)和某些日期必须是美国日期格式(例如10/22/2011 1:22:35 pm)和(例如10/22/2011 13:22:25)。

是否有任何自动方法可以在每次将字符串更改/添加到表中将其更改为UK 24h格式时将其更改为数据库中始终相同的格式？

是否可以在更新或插入时使用某些触发器来完成？有内置的功能已经做到了吗？

即使是不时运行的脚本也可以完成任务。
我正在考虑将字符串拆分为日，月，年，时，分，秒，AM / PM，然后按dd/mm顺序放置日和月部分，并以某种方式将小时部分更改为24h(如果PM，请摆脱) “am”和“pm”，然后将修改后的日期/时间放回表中。

例如表有

id datestring              value           Location   
1  15/10/2011 11:55:01 pm  BLAHBLAH        UK
2  15/10/2011 13:12:20     BLAKBLAK        GR
3  10/15/2011 6:00:01 pm   SOMESTUFF       US
4  10/15/2011 20:16:43     SOMEOTHERSTUFF  US

我们希望它成为

id datestring           value              Location
1  15/10/2011 23:55:01  BLAHBLAH           UK
2  15/10/2011 13:12:20  BLAKBLAK           GR
3  15/10/2011 18:00:01  SOMESTUFF          US
4  15/10/2011 20:16:43  SOMEOTHERSTUFF     US

我们可以使用datepart函数正确显示日期部分(日，月，年)，但是对于时间部分，我们会遇到问题，因为它更改了太多方式。

编辑以解释更多

先生。 p.campbell感谢您的编辑..我不知道如何美化它:)

和先生马修，谢谢您的快速答复。

我们可以确定是英国日期还是美国日期，因为根据PLC机器的位置，我们还有一个我没有提到的字段，其中没有文字“US”，“UK”，“GR”，“IT”。

对不起，我没有很好地解释它。我的英语不太好。

有两个不同且独立的应用程序。而且它们与sql server没有直接关系。

仅将数据写入数据库..let的应用程序简称为“writer”，而另一个读取数据的应用程序将其称为“reader”。

“作者”是PLC机器的内部应用程序，每隔1分钟将值存储到数据库中，这就是我们无法更改其行为的原因。它使用字符串数据类型根据守护程序运行的PC的区域设置以及PC与PLC机器之间的通信来在同一字段中存储日期和时间。

现在，“读者”希望日期和时间的格式为“dd / mm / yyyy 23:23:01”或“yyyy / mm / dd 23:23:01”，并且它目前唯一要做的就是在给定日期之间使用值字段中的数据进行一些计算。例如。从10/09/2011 10:00:00到15/09/2011 14:00:00。

我们只需要做这样的事情...

从table1中选择*，其中日期字符串介于“10/09/2011 10:00:00”和“15/09/2011 14:00:00”之间

我可以发布一些代码，但发布时间会很长。

Answer 1

最初，我同意马修(Matthew)的意见，但后来我意识到，鉴于所提供的信息，这实际上是有可能的(嗯，有点)。

但是，有一些警告。

通过这种方式存储和维护数据库对您没有任何帮助。最好的选择是更改应用程序，使其具有实际的Datetime值，而不是该错误的字符串。

不能按日期或时间对数据进行有意义的排序(并非不执行昂贵的字符串操作)。

您似乎所有时间都存储为本地时间，但似乎没有存储TimeZone或相关信息。没有这些信息，您将而不是能够(完全)正确地“全局”转换时间。例如，哪个是稍后的-伦敦的下午4点，或纽约的上午11点(例如国际电话 session )？答案是您不知道:这取决于一年中的时间。

您正在存储当地时间，期间。只要当地时间正确，此方法才有效。当有人将时钟设置为1900时会发生什么？您应该基于 SERVER的时钟存储时间。

您存储的时间戳基于格式化的字符串。如果用户更改了时间显示方式，则数据正确性(可能)超出了窗口。例如，如果有人删除am / pm符号，以为“我会看着窗外-如果太阳出来了，那是'am'”怎么办？

请记住所有这些。

至于如何做到这一点。
我实际上不会为此写出SQL语句。主要是因为以这种方式存储信息非常糟糕。但这也是因为我不愿做很多工作。我真的建议向任何人提供压力，让他们更改您的应用程序。

因此，我将为您提供一个非常重要的线索-仅在您的时间戳格式保持不变的情况下，这才有效。您应该能够根据字符串中是否存在“am”和“pm”来判断日期和时间的格式(如果您没有这两种格式，则表示敬酒)。正如Matthew指出的那样，日期以及时间的格式也可能有所不同-您将需要同时翻译两者。但是，由于时间戳比较，这将立即给您带来问题(请参见上面的第4点)；任何使用此数据ID进行计划或审核的尝试都注定会失败(“什么时候发生的？”“嗯，它采用的是英国日期格式，所以...”“但是这里是凌晨1点，死了!”)。

最好的答案:更改信息在数据库中的存储方式

编辑:

然后它打动了我(尤其是根据新的编辑)-可能还有其他可能使这项工作切实可行的可能性。

首先，更改数据库以根据服务器时钟实际存储某种“全局化”时间戳。
当然，这将破坏您现有的应用程序代码-它将得到数据类型不匹配错误。要解决此问题，请重命名表，然后创建一个与原始表相同的 View ，该 View 将返回“源”列中指定格式的字符串。您将需要为 View 创建instead-of触发器，以将格式化的字符串转换为实际的datetime值。最好的部分是，应用程序代码永远都不应注意到它们之间的差异。您似乎已经表明您对数据库具有足够的控制权以允许发生这种情况。这应该允许您透明地“修复”数据。

如果传入的日期时间值是绝对的(不是本地的)，这当然是最好的。希望这些值实际上应该是“插入时间”-为了使用特殊的寄存器(例如NOW或CURRENT DATE或其他)，可以安全地忽略这些值。

真不敢相信这并没有让我早日遭受打击...