sqlsql-servert-sqlstring-to-datetime

str_to_date function in sql server?

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

Question:

Is there a similar function in SQL Server?

Solution

  • 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