Removing the Time from SQL SERVER datetime

Just discovered a new trick, one that speeds up my SQL. I often use datetime columns and hate the SQL Server format where all dates include the time, which results in date conversion or bizarre arithmetics. Here ‘s a nice solution


RETURN CAST( FLOOR( CAST( @d AS INTEGER) ) AS DATETIME )

What does it do? The cast/floor/cast trio pushes the internal datetime to a float, then removes the decimal part and converts it back to a datetime, as the time portion of the datetime datatype in SQL server is stored in the decimal places of a float equivalent type, then converting to an integer removes them and sets the date to midnight.

Advertisements

5 Responses to “Removing the Time from SQL SERVER datetime”

  1. pallavi Says:

    convert(varchar(20),getdate(),108)

  2. Groucho Says:

    The initial cast should be a cast to float:

    RETURN CAST( FLOOR( CAST( @d AS FLOAT) ) AS DATETIME )

    Otherwise the time fraction will cause a round up to the next full day.

  3. SQLSimon Says:

    In the code:
    RETURN CAST( FLOOR( CAST( @d AS INTEGER) ) AS DATETIME )

    As you’re converting to INTEGER, I don’t understand the need for the FLOOR function. Why not just:

    RETURN CAST( CAST( @d AS INTEGER) AS DATETIME ) ?

    And I don’t understand Groucho’s comment about it needing to be a float.

  4. How to format datetime & date in Sql Server 2005 « Neven Palčec – Blog Says:

    […] Removing the Time from SQL SERVER datetime […]

  5. Stephan Says:

    It’s remarkable to pay a visit this web site and reading the views of all colleagues about this article, while I am also keen of getting familiarity.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: