Floor(datetime)

[ESquared]

The datetime format is represented internally in SQL server as an 8-byte number:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

This is similar to how dates are stored in other programs such as Excel worksheets, VBA, and VB.

Well, I'm accustomed to doing all sorts of date manipulation based on this fact. For example:

[COLOR=#00007F]Sub[/color] TestDate()
    [COLOR=#00007F]Dim[/color] d [COLOR=#00007F]As[/color] Date
    d = #5/7/2004 12:34:56 PM# [COLOR=green]' Note date literals in VB are enclosed in pound signs[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] [COLOR=#00007F]Int[/color](d)                  [COLOR=green]' 5/7/2004[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] d \ 1                   [COLOR=green]'  38115[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] [COLOR=#00007F]CDbl[/color](d)                 [COLOR=green]'  38114.5242592593[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] d - [COLOR=#00007F]Int[/color](d)              [COLOR=green]'  0.52425925926218[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] Format(d, "yyyymmdd")   [COLOR=green]' 20040507[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] Format(d, "hhnnss")     [COLOR=green]' 123456[/color]
    
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] Now()                   [COLOR=green]' 6/11/2004 6:22:32 PM[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] Date                    [COLOR=green]' 6/11/2004[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] Now() - Date            [COLOR=green]'  0.765648148146283[/color]
    [COLOR=#00007F]Debug[/color].[COLOR=#00007F]Print[/color] (Now() - Date) * 24     [COLOR=green]' 18.3755555555108 - 18.37 hours past midnight[/color]
[COLOR=#00007F]End[/color] [COLOR=#00007F]Sub[/color]

See all the swell things you can do? Well, I've learned how to duplicate most of them, but the one I'm still miffed about is not being able to easily chop the time off:

[COLOR=#007070]-- I thought[/color]
   Convert(datetime, Floor(Convert(real ,@date)))[/tt]
[COLOR=#007070]-- would do the trick but I found out otherwise:[/color]
   SELECT Convert(datetime,Convert(float,Convert(datetime,'9999-12-31 23:59:59.[COLOR=red]997[/color]')))
[COLOR=#007070]-- Result set:[/color]
9999-12-31 23:59:59.[COLOR=red]993[/color]

Oops!

Convert(int, @Date) rounds just like integer divide '\' in VB... not what I want.
Converting to real isn't reliable because of loss of precision.
Converting to binary is cute but then I have to do string manipulation to get the first four bytes out. I may as well do:

Convert(datetime,Convert(varchar(4),Year(@Date))+'-'+Convert(varchar(4),Month(@Date))+'-'+Convert(varchar(4),Day(@Date)))

[COLOR=#007070]--or[/color]

Convert(datetime,Convert(varchar(8),@Date,112))
[COLOR=#007070]-- notice the implicit truncation at 8 characters[/color]

Do I just have to write a UDF to do this for me? Or can anyone think of a better way to just truncate the decimal portion of a date, like how easy it is in VB: Int(d)?

 

[donutman]

Don't really understand what your looking for ee. Why isn't Convert(datetime,Convert(varchar(8),@Date,112)) good enough...because it doesn't "look" as clean as Int(d)? Convert(int, @Date-.5) not accurate enough? Better check your caffeine level.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

hmmmmmmmmmmmmm

honsetly I never thought of Convert(datetime,Convert(varchar(8),@Date,112)) until I was writing this post. And I thought of the -.5 thing while writing it too, but didn't check it.

Sighhhhhhhhhhhhhhhhhhhhhh you know I'm a perfectionist!!!