How to convert from string to datetime in sql server - Queries

Please watch below video for complete explanation

--How to convert from string to datetime in sql server
-- Please execute below queries or sql script in SQL Server management studio and learn, how to convert string to datetime format. Here i am using CONVERT and CAST functions for converting string dates, string datetime data to the sql server datetime datatype.

--SQL server Script converting string to date, string to datetime conversion
-- Microsoft sql server script for converting chat to date conversion - convert string to datetime - -- how to convert varchar to date in sql server
-- in the below query you can convert string(MMM DD YYYY MM:SSAM) to datetime

select convert(datetime, 'Mar 7 2016 07:01AM', 100) -- string format should be - mon dd yyyy hh:mmAM (or you can use PM)
-- 2016-03-07 07:01:00.000
SELECT convert(datetime, 'Mar 7 2016 07:01AM') -- 2016-03-07 07:01:00.000

-- convert string(MMM DD YY MM:SSAM) to datetime
SELECT convert(datetime, 'Mar 7 2016 07:01AM', 0) -- string format mon dd yy hh:mmAM (or PM) to datetime conversion
SELECT convert(datetime, 'Mar 7 2016 07:01AM') -- 2016-03-07 07:01:00.000

-- different types of sql server date formats, convert string to datetime in sql server,convert string to date in sql server query, SQL Server convert string to date
-- T-SQL script for convert from string to datetime. Here 101,102,103 are string format code for datetime
SELECT convert(datetime, '12/28/2016', 101) -- string format mm/dd/yyyy to datetime in sql server
SELECT convert(datetime, '2016.12.28', 102) -- string yyyy.mm.dd ANSI date with century to datetime
SELECT convert(datetime, '28/12/2016', 103) -- string format dd/mm/yyyy to datetime in sql server
SELECT convert(datetime, '28.12.2016', 104) -- string format dd.mm.yyyy to datetime in sql server
SELECT convert(datetime, '28-12-2016', 105) -- string format dd-mm-yyyy to datetime in sql server query
--output : 2016-12-28 00:00:00.000

-- depending on language settings, some of date strings are non deterministic types are there. Below queries will help how to convert nondeterministic string to datetime converstion in sql server query.
SELECT convert(datetime, '28 DEC 2016', 106) -- convert dd mon yyyy to datetime in sql server
SELECT convert(datetime, 'Dec 28, 2016', 107) -- convert mon dd, yyyy to datetime in sql server
-- output 2016-12-28 00:00:00.000
SELECT convert(datetime, '20:10:44', 108) -- query to convert hh:mm:ss to datetime in sql server
-- 1900-01-01 20:10:44.000

-- convert dd yyyy hh:mm:ss:mmmAM (or PM) to datetime in MSSQL server(query)- sql time format - SQL Server datetime format
SELECT convert(datetime, 'Dec 28 2016 12:01:47:034PM', 109)
-- 2016-12-28 12:01:47.033
SELECT convert(datetime, '12-28-2016', 110) -- query to convert mm-dd-yyyy to datetime in sqlserver
SELECT convert(datetime, '2016/12/28', 111) -- query to convert yyyy/mm/dd to datetime in sqlserver
-- string format YYYYMMDD is the ISO date format and it works in any language, basically this is international standard format
SELECT convert(datetime, '20161228')--2016-12-28 00:00:00.000
SELECT convert(datetime, '20161228', 112) -- with format code(112) - convert ISO yyyymmdd to datetime using sql server query
-- 2016-12-28 00:00:00.000

SELECT convert(datetime, '28 Dec 2016 12:22:27:677', 113) -- convert string format dd mon yyyy hh:mm:ss:mmm to datetime using sql server query
-- 2016-12-28 12:22:27.677

--sql query to convert string hh:mm:ss:mmm(24h) to datetime in sql server
SELECT convert(datetime, '22:12:27:200', 114) 
-- 1900-01-01 22:12:27.200

--sql server query to convert string yyyy-mm-dd hh:mm:ss(24h) to datetime
SELECT convert(datetime, '2016-12-28 20:44:11', 120) 
-- 2016-12-28 20:44:11.000
SELECT convert(datetime, '2016-12-28 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-12-28 20:44:11.500

-- string format 126 is ISO 8601 format: this is one of the international standard and this format is useful for any language conversation.
SELECT convert(datetime, '2014-12-28T19:50:40.345', 126) -- query to convert string yyyy-mm-ddThh:mm:ss(.mmm) to date time in sql server
-- 2014-12-28 19:50:40.347

-- query to convert string DDMMYYYY format to datetime in sql server
SELECT convert(datetime, STUFF(STUFF('26032016',3,0,'-'),6,0,'-'), 105)
-- 2016-03-26 00:00:00.000

-- query to convert without century string to datetime conversion in sql server
-- convert nondeterministic(different language formats) string format to datetime.
SELECT convert(datetime, 'Dec 23 16 11:02:44AM') -- convert Default string to datetime in sql server
--2016-12-23 11:02:44.000
SELECT convert(datetime, '12/28/16', 1) -- query to convert string mm/dd/yy U.S. to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, '16.12.28', 2) -- query to convert string yy.mm.dd ANSI to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, '28/12/16', 3) -- query to convert string dd/mm/yy UK/FR to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, '28.12.16', 4) -- query to convert string dd.mm.yy German to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, '28-12-16', 5) -- query to convert string dd-mm-yy Italian to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, '28 Dec 16', 6) -- query to convert string dd mon yy non-det to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, 'Dec 28, 16', 7) -- query to convert string mon dd, yy non-det to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, '20:12:44', 8) -- query to convert string hh:mm:ss to datetime in sql server
--1900-01-01 20:12:44.000
SELECT convert(datetime, 'Dec 28 16 11:02:44:013AM', 9) -- query to convert string Default with msec to datetime in sql server
--2016-12-28 11:02:44.013
SELECT convert(datetime, '16/12/28', 11) -- query to convert string yy/mm/dd Japan to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, '161228', 12) -- query to convert string yymmdd ISO to datetime in sql server
--2016-12-28 00:00:00.000
SELECT convert(datetime, '28 Dec 16 11:02:07:577', 13) -- query to convert string dd mon yy hh:mm:ss:mmm EU dflt to datetime in sql server
--2016-12-28 11:02:07.577
SELECT convert(datetime, '20:12:25:300', 14) -- query to convert string hh:mm:ss:mmm(24h) to datetime in sql server
--1900-01-01 20:12:25.300
SELECT convert(datetime, '2016-12-28 20:44:11',20) -- query to convert string yyyy-mm-dd hh:mm:ss(24h) ODBC can. to datetime in sql server
--2016-12-28 20:44:11.000
SELECT convert(datetime, '2016-12-28 20:44:11.500', 21)-- query to convert string yyyy-mm-dd hh:mm:ss.mmm ODBC to datetime in sql server
--2016-12-28 20:44:11.500

If you have any queries or suggestions, please feel free to ask in comments section.