Home Forums Article Discussions Article Discussions by Author Discuss content posted by Seth Phelabaum Date Manipulation with DATEADD/DATEDIFF RE: Date Manipulation with DATEADD/DATEDIFF

  • April 7, 2010 at 11:26 am

    #1147589

    Elaine Shafer-401422 (4/7/2010)

    I frequently use SELECT CAST(FLOOR(CAST(<yourDateHere> AS FLOAT)) AS DATETIME)

    Interestingly, rounding happens near midnight. The following returns '2010-04-08 00:00:00.000':

    DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'

    SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)

    Not sure if this timestamp would actually happen...

    As mentioned in the article, Datetime is only accurate to 3ms. 999 and 998 both round up to 000. It's not your float conversion doing the rounding, it's the limitations of the datetime data type. Run this:

    DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'

    SELECT @date

    SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]