Casting string date & time together and separately

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

-- 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 cast string to datetime

-- 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        

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