DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE