How to convert from string to date? Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the conversion from string to date (DATE, DATETIME, SMALLDATETIME). When a conversion involves month or day name, it is language setting dependent, therefore non-deterministic. The week function is also non-deterministic because it depends on the first date setting for the week (default in sys.syslanguages table or override by SET DATEFIRST). USE AdventureWorks SET LANGUAGE us_english -- Convert string to date using style (format) numbers - sql dates format -- SQL convert text to date - SQL convert string to date / datetime SELECT convert(datetime,'15/03/18',3) -- 2018-03-15 00:00:00.000 SELECT convert(datetime,'15.03.18',4) -- 2018-03-15 00:00:00.000 -- Convert datetime to text style (format) list - sql time format -- SQL Server without century (YY) date styles (there are exceptions!) -- Generally adding 100 to style number results in century format CCYY / YYYY SELECT convert(varchar,getdate()) -- Mar 15 2018 10:35AM SELECT convert(varchar,getdate(),0) -- Mar 15 2018 10:35AM SELECT convert(varchar,getdate(),1) -- 03/15/18 SELECT convert(varchar,getdate(),2) -- 18.03.15 SELECT convert(varchar,getdate(),3) -- 15/03/18 SELECT convert(varchar,getdate(),4) -- 15.03.18 SELECT convert(varchar,getdate(),5) -- 15-03-18 SELECT convert(varchar,getdate(),6) -- 15 Mar 18 SELECT convert(varchar,getdate(),7) -- Mar 15, 18 SELECT convert(varchar,getdate(),8) -- 10:39:39 SELECT convert(varchar,getdate(),9) -- Mar 15 2018 10:39:48:373AM SELECT convert(varchar,getdate(),10) -- 03-15-18 SELECT convert(varchar,getdate(),11) -- 18/03/15 SELECT convert(varchar,getdate(),15) -- 180315 SELECT convert(varchar,getdate(),13) -- 15 Mar 2018 10:41:07:590 SELECT convert(varchar,getdate(),14) -- 10:41:25:903 SELECT convert(varchar,getdate(),20) -- 2018-03-15 10:43:56 SELECT convert(varchar,getdate(),21) -- 2018-03-15 10:44:04.950 SELECT convert(varchar,getdate(),22) -- 03/15/18 10:44:50 AM SELECT convert(varchar,getdate(),23) -- 2018-03-15 SELECT convert(varchar,getdate(),24) -- 10:45:45 SELECT convert(varchar,getdate(),25) -- 2018-03-15 10:46:11.263



-- T-SQL with century (YYYY or CCYY) datetime styles (formats)

SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)

SELECT convert(varchar, getdate(), 101) -- 10/23/2016 SELECT convert(varchar, getdate(), 102) -- 2016.10.23 SELECT convert(varchar, getdate(), 103) -- 23/10/2016 SELECT convert(varchar, getdate(), 104) -- 23.10.2016 SELECT convert(varchar, getdate(), 105) -- 23-10-2016 SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016 SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016 SELECT convert(varchar, getdate(), 108) -- 09:10:34 SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM) SELECT convert(varchar, getdate(), 110) -- 10-23-2016 SELECT convert(varchar, getdate(), 111) -- 2016/10/23 SELECT convert(varchar, getdate(), 112) -- 20161023 SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383 SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h) SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h) SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383 SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383 GO -- SQL cast string to datetime - time part 0 - sql hh mm -- SQL Server cast string to DATE (SQL Server 2008 feature) - sql yyyy mm dd SELECT [Date] = CAST('20120228' AS date) -- 2012-02-28 SELECT [Datetime] = CAST('20120228' AS datetime) -- 2012-02-28 00:00:00.000 SELECT [Datetime] = CAST('20120228' AS smalldatetime) -- 2012-02-28 00:00:00 -- SQL convert string to datetime - time part 0 -- SQL Server convert string to date - sql times format SELECT [Datetime] = CONVERT(datetime,'2010-02-28') SELECT [Datetime] = CONVERT(smalldatetime,'2010-02-28') SELECT [Datetime] = CAST('Mar 15, 2010' AS datetime) SELECT [Datetime] = CAST('Mar 15, 2010' AS smalldatetime) SELECT [Datetime] = CONVERT(datetime,'Mar 15, 2010') SELECT [Datetime] = CONVERT(smalldatetime,'Mar 15, 2010') SELECT [Datetime] = CAST('Mar 15, 2010 12:07:34.444' AS datetime) SELECT [Datetime] = CAST('Mar 15, 2010 12:07:34.444' AS smalldatetime) SELECT [Datetime] = CONVERT(datetime,'Mar 15, 2010 12:07:34.444') SELECT [Datetime] = CONVERT(smalldatetime,'Mar 15, 2010 12:07:34.444') SELECT [Datetime] = CAST('2010-02-28 12:07:34.444' AS datetime) SELECT [Datetime] = CAST('2010-02-28 12:07:34.444' AS smalldatetime) SELECT [Datetime] = CONVERT(datetime,'2010-02-28 12:07:34.444') SELECT [Datetime] = CONVERT(smalldatetime,'2010-02-28 12:07:34.444') -- Double conversion SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS datetime) SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS smalldatetime) SELECT [Datetime] = CONVERT(datetime,convert(varchar,getdate())) SELECT [Datetime] = CONVERT(smalldatetime,convert(varchar,getdate())) ------------ -- MSSQL convert date string to datetime - time is set to 00:00:00.000 or 12:00AM PRINT CONVERT(datetime,'07-10-2016',110) -- Jul 10 2016 12:00AM PRINT CONVERT(datetime,'2016/07/10',111) -- Jul 10 2016 12:00AM PRINT CONVERT(varchar,CONVERT(datetime,'20160710', 112),121) -- 2016-07-10 00:00:00.000 ------------ -- Selected named date styles DECLARE @DateTimeValue varchar(32) -- US-Style -- Convert string to datetime sql - sql convert string to datetime SELECT @DateTimeValue = '10/23/2016' SELECT StringDate=@DateTimeValue, [US-Style] = CONVERT(datetime, @DatetimeValue) SELECT @DateTimeValue = '10/23/2016 23:01:05' SELECT StringDate = @DateTimeValue, [US-Style] = CONVERT(datetime, @DatetimeValue) -- UK-Style, British/French SELECT @DateTimeValue = '23/10/16 23:01:05' SELECT StringDate = @DateTimeValue, [UK-Style] = CONVERT(datetime, @DatetimeValue, 3) SELECT @DateTimeValue = '23/10/2016 04:01 PM' SELECT StringDate = @DateTimeValue, [UK-Style] = CONVERT(datetime, @DatetimeValue, 103) -- German-Style SELECT @DateTimeValue = '23.10.16 23:01:05' SELECT StringDate = @DateTimeValue, [German-Style] = CONVERT(datetime, @DatetimeValue, 4) SELECT @DateTimeValue = '23.10.2016 04:01 PM' SELECT StringDate = @DateTimeValue, [German-Style] = CONVERT(datetime, @DatetimeValue, 104) -- Double conversion to US-Style 107 with century: Oct 23, 2016 SET @DateTimeValue='10/23/16' SELECT StringDate=@DateTimeValue, [US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107) -- SQL dateformat setting USE AdventureWorks2008; SELECT convert(datetime,'14/05/08') /* Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. */ SET DATEFORMAT ymd SELECT convert(datetime,'14/05/08') -- 2014-05-08 00:00:00.000 -- Setting DATEFORMAT to UK-Style SET DATEFORMAT dmy SELECT convert(datetime,'20/05/14') -- 2014-05-20 00:00:00.000 -- Setting DATEFORMAT to US-Style SET DATEFORMAT mdy SELECT convert(datetime,'05/20/14') -- 2014-05-20 00:00:00.000 SELECT convert(datetime,'05/20/2014') -- 2014-05-20 00:00:00.000

GO ------------ -- SQL date & time eliminating dividing characters ------------ -- MSSQL replace string function -- T-SQL string concatenate (+) USE AdventureWorks2008; SELECT replace(convert(VARCHAR(10),getdate(),102),'.','') -- 20120315 SELECT replace(convert(VARCHAR(10),getdate(),111),'/','') -- 20120315 -- SQL triple replace SELECT replace(replace(replace(convert(VARCHAR(25), getdate(),20),'-',''), ':',''),' ','') -- 20120529090427 -- T-SQL concatenating from a date and a time conversion SELECT replace(convert(VARCHAR(10),getdate(),111),'/','') + replace(convert(VARCHAR(8),getdate(),108),':','') -- 20120315085654 ------------ -- Converting string dates from a table ------------ -- Create and populate a test table with a string date USE tempdb; SELECT DepartmentID, LastUpdate=CONVERT(varchar, dateadd(day, DepartmentID, ModifiedDate),100) INTO DeptInfo FROM AdventureWorks.HumanResources.Department SELECT * FROM DeptInfo /* Partial results DepartmentID LastUpdate 1 Jun 2 1998 12:00AM 2 Jun 3 1998 12:00AM */ -- Convert string date column to datetime SELECT DepartmentID, LastChangeDate=convert(datetime, LastUpdate) FROM DeptInfo /* Partial results DepartmentID LastChangeDate 1 1998-06-02 00:00:00.000 2 1998-06-03 00:00:00.000 */ DROP TABLE DeptInfo GO The BEST 70-461 SQL Server 2012 Querying Exam Prep Book! ------------------------------------------------------- -- Casting string date & time together and separately

------------------------------------------------------- -- SQL cast string to datetime SELECT CAST('20100315 16:40:31' AS datetime) -- Result: 2010-03-15 16:40:31.000 -- SQL cast string to date - time part 0 SELECT CAST('20100315' AS datetime) -- Result: 2010-03-15 00:00:00.000 -- SQL cast string to time - date part 1900-01-01 SELECT CAST('16:40:31' AS smalldatetime) -- Result: 1900-01-01 16:41:00 ------------ -- SQL DATEDIFF with string date ------------ DECLARE @sDate varchar(10) SET @sDate = '2010/03/15' -- DATEDIFF (delta) between two dates in months SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), @sDate) SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CAST(@sDate as datetime)) SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate)) SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,111)) -- Seme results for above: 2008-12-29 11:04:51.097 15 -- SQL convert to datetime with wrong style (111 correct, 112 incorrect) SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,112)) /* ERROR Msg 241, Level 16, State 1, Line 11 Conversion failed when converting date and/or time from character string. */ ------------ ------------ -- SQL Server date string search guidelines - comparing dates ------------ -- Date equal search DECLARE @Date1 datetime, @Date2 datetime, @Date3 datetime SET @Date1 = '2012-01-01' SET @Date2 = '2012-01-01 00:00:00.000' SET @Date3 = '2012-01-01 11:00' SELECT @Date1, @Date2, @Date3 -- Date-only @Date1 is translated to datetime -- 2012-01-01 00:00:00.000 2012-01-01 00:00:00.000 2012-01-01 11:00:00.000 -- The following is a datetime comparison, not a date-only comparison IF (@Date1 = @Date2) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL' -- EQUAL -- Equal test fails because time parts are different IF (@Date1 = @Date3) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL' -- NOT EQUAL -- The string date implicitly converted to datetime for the equal test IF ('2012-01-01' = @Date3) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL' -- NOT EQUAL -- Safe way to search for a specific date SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader WHERE '2004/02/01' = CONVERT(varchar, OrderDate,111) -- 244 -- Equivalent to SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader WHERE OrderDate BETWEEN '2004/02/01 00:00:00.000' AND '2004/02/01 23:59:59.997' -- 244 -- Safe way to search for a specific date range SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader WHERE CONVERT(varchar, OrderDate,111) BETWEEN '2004/02/01' AND '2004/02/14' -- 1059 -- Equivalent to SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader WHERE OrderDate BETWEEN '2004/02/01 00:00:00.000' AND '2004/02/14 23:59:59.997' -- 1059 SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader WHERE OrderDate >= '2004/02/01 00:00:00.000' AND OrderDate < '2004/02/15 00:00:00.000' -- 1059 ------------ ------------ -- SQL Server convert from string to smalldatetime ------------ -- T-SQL convert from format mm/dd/yyyy to smalldatetime SELECT CONVERT(smalldatetime, '10/23/2016', 101) -- 2016-10-23 00:00:00 -- MSSQL convert from format dd/mm/yyyy to smalldatetime SELECT CONVERT(smalldatetime, '23/10/2016', 103) -- 2016-10-23 00:00:00 -- Month 23 is out of range SELECT CONVERT(smalldatetime, '23/10/2016', 101) /* Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value. */ ------------ -- Translate/convert string/text hours and minutes to seconds ------------ DECLARE @TimeStr varchar(16) = '20:49:30' SELECT PARSENAME(REPLACE(@TimeStr,':','.'),1) + PARSENAME(REPLACE(@TimeStr,':','.'),2) * 60 + PARSENAME(REPLACE(@TimeStr,':','.'),3) * 3600 -- 74970 ------------ -- Related articles:



http://sqlusa.com/bestpractices/datetimeconversion/ CAST and CONVERT (Transact-SQL) 10 reasons to explicitly convert SQL Server data types