如何将字符串解析为日期？

小开

CONVERT(DateTime, DateField, 104)

小开

最佳答案

CONVERT(datetime, '24.04.2012', 104)

Should do the trick. See here for more info: CAST and CONVERT (Transact-SQL)

小开

You can use:

SELECT CONVERT(datetime, '24.04.2012', 103) AS Date

Reference: CAST and CONVERT (Transact-SQL)

小开

Although the CONVERT thing works, you actually shouldn't use it. You should ask yourself why you are parsing string values in SQL-Server. If this is a one-time job where you are manually fixing some data you won't get that data another time, this is ok, but if any application is using this, you should change something. Best way would be to use the "date" data type. If this is user input, this is even worse. Then you should first do some checking in the client. If you really want to pass string values where SQL-Server expects a date, you can always use ISO format ('YYYYMMDD') and it should convert automatically.

小开

Assuming that the database is MS SQL Server 2012 or greater, here's a solution that works. The basic statement contains the in-line try-parse:

SELECT TRY_PARSE('02/04/2016 10:52:00' AS datetime USING 'en-US') AS Result;

Here's what we implemented in the production version:

UPDATE dbo.StagingInputReview
SET ReviewedOn =
ISNULL(TRY_PARSE(RTrim(LTrim(ReviewedOnText)) AS datetime USING 'en-US'), getdate()),
ModifiedOn = (getdate()), ModifiedBy = (suser_sname())
-- Check for empty/null/'NULL' text
WHERE not ReviewedOnText is null
AND RTrim(LTrim(ReviewedOnText))<>''
AND Replace(RTrim(LTrim(ReviewedOnText)),'''','') <> 'NULL';

The ModifiedOn and ModifiedBy columns are just for internal database tracking purposes.

See also these Microsoft MSDN references:

小开

CONVERT(DateTime, ExpireDate, 121) AS ExpireDate

will do what is needed, result:

2012-04-24 00:00:00.000