How to parse string into date

Question

How can I convert a String to a Date in T-SQL   My test case is the string   24 04 2012

User · Answer

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:

User · Answer

CONVERT DateTime  ExpireDate  121  AS ExpireDate   will do what is needed  result   2012-04-24 00 00 00 000

User · Answer

Microsoft SQL Date Formats  CONVERT DateTime  DateField  104

User · Answer

You can use   SELECT CONVERT datetime   24 04 2012   103  AS Date   Reference  CAST and CONVERT  Transact-SQL

User · Answer

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

User · Answer

CONVERT datetime   24 04 2012   104    Should do the trick  See here for more info  CAST and CONVERT  Transact-SQL

[tsql] How to parse string into date?

Examples related to tsql

Examples related to sql-server-2005