How to parse string into date?
How can I convert a String to a Date in T-SQL?
My test case is the string:
Read more... Read less...
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:
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.
CONVERT(DateTime, ExpireDate, 121) AS ExpireDate
will do what is needed, result: