str_to_date function in sql server?

Question

MySQL has a function called STR_TO_DATE, that converts a string to date.

Question:

Is there a similar function in SQL Server?

Answer 1

If you need to parse a particular format, use CONVERT(datetime, @mystring, @format). Use this as a reference: https://web.archive.org/web/20200729210252/http://www.sqlusa.com/bestpractices/datetimeconversion/

Some examples:

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

-- mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

-- 1900-01-01 20:10:44.000

Answer 2

What if the string is 7/7/2010?

Then use CONVERT with either 101 (mm/dd/yy) or 103 (dd/mm/yy) depending on what you want:

SELECT CONVERT(DATE, '7/7/2010', 103)

Result:

2010-07-07

Answer 3

CAST(<string> AS DATETIME)

Answer 4

Use CAST.

declare @MyString varchar(10)
declare @MyDate datetime

set @MyString = '2010-08-19'
set @MyDate = cast(@MyString as datetime)
select @MyDate

Answer 5

Here is a good example:

declare @myDate datetime
set @myDate = '06/09/2017'

select concat(convert(varchar(20), @myDate,101), ' -- ', 
              convert(varchar(20), @myDate,103), ' -- ',
              convert(varchar(20), @myDate,6))

This is what you get, depending on 101 or 103 or 6:

09/06/2017 -- 06/09/2017 -- 06 Sep 17

A good summary of types of dates is here - https://www.w3schools.com/sql/func_convert.asp

Answer 6

On MSSQL: select cast('2012/06/12 10:32AM' as datetime);

You will get it: 2012-06-12 10:32:00.000