How to convert a string to date T-SQL?

Question

How to convert String to Date in T-SQL?

My test case is the line: '24.04.2012'

Answer 1

 CONVERT(datetime, '24.04.2012', 104) 

Gotta do the trick. See here for more information: "CAST and CONVERT (Transact-SQL)"

Answer 2

Microsoft SQL Date Formats

 CONVERT(DateTime, DateField, 104) 

Answer 3

The solution works well here. The main statement contains a built-in try-parse method:

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

Here 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 for internal database tracking only.

See also these Microsoft MSDN links:

• CAST and CONVERT (Transact-SQL)
• PARSE (Transact-SQL)

Answer 4

Although CONVERT works, you should not actually use it. You should ask yourself why you parse string values ​​in SQL-Server. If this is a one-time task in which you manually correct some data, you will not receive this data another time, this is normal, but if any application uses this, you must change something. A better way would be to use the date data type. If this is user input, it is even worse. Then you first need to perform a client check. If you really want to pass string values ​​where SQL-Server expects a date, you can always use the ISO format ("YYYYMMDD"), and it should be automatically converted.

Answer 5

You can use:

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

Reference: CAST and CONVERT (Transact-SQL)

Answer 6

 CONVERT(DateTime, ExpireDate, 121) AS ExpireDate 

will do what is necessary, the result:

 2012-04-24 00:00:00.000