Execute the following T-SQL scripts in Microsoft SQL Server Manangement Studio Query Editor to demonstrate T-SQL convert and cast functions in transforming string date, string time & string datetime data to datetime data type. T-SQL date / datetime functions usage examples are presented as well.

-- SQL Server string to date / datetime conversion - datetime string format sql server

-- MSSQL string to datetime conversion - convert char to date - convert varchar to date

-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000

 

-- Without century (yy) string date conversion - convert string to datetime

SELECT convert(datetime, 'Oct 23 12 11:01AM',     0) -- mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000

 

-- Convert string to datetime sql - convert string to date sql - sql dates format

-- T-SQL convert string to datetime - SQL Server convert string to date 

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

SELECT convert(datetime, '2016.10.23',          102) -- yyyy.mm.dd

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

 

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)

-- 2016-10-23 11:02:44.013

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

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

SELECT convert(datetime, '20161023',            112) -- yyyymmdd

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

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm

-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300',             114) -- hh:mm:ss:mmm(24h)

-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11',      120) -- yyyy-mm-dd hh:mm:ss(24h)

-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500',  121) -- yyyy-mm-dd hh:mm:ss.mmm

-- 2016-10-23 20:44:11.500

SELECT convert(datetime, '2008-10-23T18:52:47.513',  126) -- yyyy-mm-ddThh:mm:ss.mmm

-- 2008-10-23 18:52:47.513


-- Convert DDMMYYYY format to datetime - sql server to date / datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL string to datetime conversion without century - some exceptions

SELECT convert(datetime, '10/23/16',          1)                  -- mm/dd/yy

SELECT convert(datetime, '16.10.23',          2)                  -- yy.mm.dd

SELECT convert(datetime, '23/10/16',          3)                  -- dd/mm/yy

SELECT convert(datetime, '23.10.16',          4)                  -- dd.mm.yy

SELECT convert(datetime, '23-10-16',          5)                  -- dd-mm-yy

SELECT convert(datetime, '23 OCT 16',         6)                  -- dd mon yy

SELECT convert(datetime, 'Oct 23, 16',        7)                  -- mon dd, yy

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

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9)

SELECT convert(datetime, '10-23-16',          10)                 -- mm-dd-yy

SELECT convert(datetime, '16/10/23',          11)                 -- yy/mm/dd

SELECT convert(datetime, '161023',            12)                 -- yymmdd

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13)        -- dd mon yy hh:mm:ss:mmm

SELECT convert(datetime, '20:10:25:300',        14)           -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11',20)            -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)       -- yyyy-mm-dd hh:mm:ss.mmm

------------

 

-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss

-- String to datetime - mssql datetime - sql convert date - sql concatenate string

DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)

 

SELECT      @DateValue = '20120718',

            @TimeValue = '211920'

 

SELECT @DateTimeValue =

            convert(varchar, convert(datetime, @DateValue), 111)

            + ' ' + substring(@TimeValue, 1, 2)

            + ':' + substring(@TimeValue, 3, 2)

            + ':' + substring(@TimeValue, 5, 2)

 

SELECT

      DateInput = @DateValue, TimeInput = @TimeValue,

      DateTimeOutput = @DateTimeValue;

/*

DateInput   TimeInput   DateTimeOutput

20120718    211920      2012/07/18 21:19:20

*/

 

-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm

DECLARE  @Seconds INT

SET @Seconds = 20000

SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)

GO

/* HH    MM    SS

  5     33    20   */
------------

 

-- SQL Server 2008 convert datetime to date - sql yyyy mm dd

SELECT      TOP (3)  OrderDate = CONVERT(date, OrderDate),

            Today = CONVERT(date, getdate())

FROM AdventureWorks2008.Sales.SalesOrderHeader

ORDER BY newid();

/*          OrderDate   Today

            2003-07-09  2012-06-18

            2003-09-26  2012-06-18

            2004-02-15  2012-06-18 */

------------

-- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

/*  YYYY/MM/DD

    2015/07/11    */

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]

/*  YYYYMMDD

    20150711     */

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),'/',' ') AS [YYYY MM DD]

/* YYYY MM DD

   2015 07 11    */

-- Converting to special (non-standard) date fomats: DD-MMM-YY
SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),' ','-'))
-- 07-MAR-14
------------

-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM

PRINT CONVERT(datetime,'07-10-2012',110)        -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'2012/07/10',111)        -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'20120710',  112)        -- Jul 10 2012 12:00AM          

------------     

 

-- String to date conversion - sql date yyyy mm dd - sql date formatting

-- SQL Server cast string to date - sql convert date to datetime

SELECT [Date] = CAST (@DateValue AS datetime)

GO

-- 2012-07-18 00:00:00.000