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
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
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
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
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))
Above SQL query is very much helpfull for me. I refer this from sqlusa.com
