How to parse string into date?
CONVERT(datetime, '24.04.2012', 104)
Should do the trick. See here for more info: CAST and CONVERT (Transact-SQL)
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:
- CAST and CONVERT (Transact-SQL)
- PARSE (Transact-SQL)
Microsoft SQL Date Formats
CONVERT(DateTime, DateField, 104)