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 Server date string search guidelines - comparing dates

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

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

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