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

Microsoft SQL Date Formats  CONVERT DateTime  DateField  104

User · Answer

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

User · Answer

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

User · Answer

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

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   lt  gt       AND Replace RTrim LTrim ReviewedOnText             lt  gt   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

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

[tsql] How to parse string into date?

Examples related to tsql

Examples related to sql-server-2005