SQL Server将字符串转换为datetime

This is not asking how to convert an arbitrary string to datetime in MSSQL such as this question.

这并不是问如何在MSSQL中将任意字符串转换为datetime，比如这个问题。

I can control the string format but I want to know what the MSSQL syntax is for updating a datetime field using a date string.

我可以控制字符串格式，但我想知道使用日期字符串更新datetime字段的MSSQL语法是什么。

2 个解决方案

#1

UPDATE MyTable SET MyDate = CONVERT(datetime, '2009/07/16 08:28:01', 120)

For a full discussion of CAST and CONVERT, including the different date formatting options, see the MSDN Library Link below:

有关CAST和CONVERT的完整讨论，包括不同的日期格式化选项，请参见下面的MSDN库链接:

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

#2

For instance you can use

例如，你可以使用

update tablename set datetimefield='19980223 14:23:05'
update tablename set datetimefield='02/23/1998 14:23:05'
update tablename set datetimefield='1998-12-23 14:23:05'
update tablename set datetimefield='23 February 1998 14:23:05'
update tablename set datetimefield='1998-02-23T14:23:05'

You need to be careful of day/month order since this will be language dependent when the year is not specified first. If you specify the year first then there is no problem; date order will always be year-month-day.

您需要注意日/月的顺序，因为当没有首先指定年份时，这将依赖于语言。如果你先明确年份，那么就没有问题;订单日期将始终为年月日。

#1

UPDATE MyTable SET MyDate = CONVERT(datetime, '2009/07/16 08:28:01', 120)