Time Travel!

[LNBruno]

I'll be replacing the following with a DateAdd(DateDiff()) method...

DECLARE @datetime DATETIME
SET @datetime = '7/5/2007 11:59:59 PM'

-- so what day is it?
SELECT CAST(CAST(@DateTime AS INTEGER) AS DATETIME)

-- and what time of day? on what day?
SELECT CAST(@DateTime - CAST(@DateTime AS INTEGER) AS DATETIME)

< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLDenis]

DECLARE @datetime DATETIME
SET @datetime = '7/5/2007 11:59:59.999'

SELECT @datetime
GO

DECLARE @datetime DATETIME
SET @datetime = '7/5/2007 11:59:59.998'

SELECT @datetime
GO

How Are Dates Stored In SQL Server?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

This would make the point of my post more effective

DECLARE @datetime DATETIME
SET @datetime = '7/5/2007 23:59:59.999'

SELECT @datetime
GO

DECLARE @datetime DATETIME
SET @datetime = '7/5/2007 23:59:59.998'

SELECT @datetime
GO

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Exactly, Denis! Precision is critical.

It seems that here, CAST rounds to the nearest integer rather than truncating to the lower (as cast does most other places).





< M!ke >
I am not a hamster and life is not a wheel.

 

[bborissov]

Not exactly correct.

declare @test float
SET @Test = 1.99999
SELECT @test, CAST(@Test as int)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Lamprey13]

Hopefully this might help a little. DATETIME columns only have 3.33 millisecond accuracy. Per BOL here is how SQL Server "converts" a given date-time to a accuracy that SQL supports:

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

Example                 Rounded example  
---------------------   -----------------------
01/01/98 23:59:59.999   1998-01-02 00:00:00.000
 
01/01/98 23:59:59.995,  1998-01-01 23:59:59.997
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, 
01/01/98 23:59:59.998

01/01/98 23:59:59.992,  1998-01-01 23:59:59.993
01/01/98 23:59:59.993, 
01/01/98 23:59:59.994

01/01/98 23:59:59.990   1998-01-01 23:59:59.990
01/01/98 23:59:59.991

 

[LNBruno]

So you wouldn't recommend something like this as a long-term solution?

CREATE TABLE MyCalendar
   (
   [Day of Year]     VARCHAR(10)
   , [Day]           VARCHAR(10)
   , [Day of Week]   VARCHAR(10)
   , [Day of Month]  VARCHAR(10)
   , [Week]          VARCHAR(10)
   , [Month]         VARCHAR(10)
   , [Quarter]       VARCHAR(10)
   , [Year]          VARCHAR(10)
   , [Hour]          VARCHAR(10)
   , [Minute]        VARCHAR(10)
   , [Second]        VARCHAR(10)
   , [Millisecond]   VARCHAR(10)
   )

< M!ke >
I am not a hamster and life is not a wheel.