DaveWentzel.com            All Things Data

Data Types

 

Date/Time
 
I find this interesting:
dbcc useroptions
 
SET language british

SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000

SET language us_english --Changed language setting to us_english.

SELECT CAST('14/2/2006' AS datetime) --**ERROR!***

SET dateformat 'dmy' --to override the language default

SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000

SET language british SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000

SET language us_english --Changed language setting to us_english.

SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000

 
Performance Implications of datetime conversions
--date/time conversion methods, stripping the date or time component,

--and their performance implications (iterate 1000000 times in a loop)

--case 1:  convert to char and back, eliminating the time component

--this is slowest


SELECT CAST(CONVERT(char(8),getdate(),112) AS DATETIME) --'YYYYMMDD'

--give me time to the nearest ms


SELECT CAST(CONVERT(CHAR(12),GETDATE(),114) AS DATETIME)

--to the nearest second

SELECT CAST(CONVERT(CHAR(8),GETDATE(),114) AS DATETIME)

--case 2:  set time portion to midnight by peforming integer math

--this is the fastest, but only slightly better than case 3, which is

--probably more logical for most programmers and more elegant.


SELECT CAST(CAST(GETDATE()-0.50000004 AS INT) AS DATETIME)

--case 3:  calculate the offset using DATEADD/DATEDIFF

SELECT DATEADD(day,DATEDIFF(day,'19000101',getdate()),'19000101')

--the logic can be shortened by remembering that converting integer 0

--to a datetime yields the base date at midnight


SELECT DATEADD(day,DATEDIFF(day,0,getdate()),0)

--give me the time only portion, to ms accuracy

SELECT DATEADD(ms,DATEDIFF(ms,DATEADD(day,DATEDIFF(day,0,getdate()),0),getdate()),0)

--same example, but accuracy to the second

SELECT DATEADD(second,DATEDIFF(second,DATEADD(day,DATEDIFF(day,0,getdate()),0),getdate()),0)
 
 
 

Add new comment