Removing the time from a datetime database field

  • hamishahern ,

    If you cast directly to int depending on the time portion of the datetime value you may get different DATES but , if you really need to do it, you can use the workaround that Frank posted above!

     


    * Noel

  • Simple.
    select CAST(FLOOR(CAST(GETDATE() as float)) as datetime)
    This:
    1: Converts a datetime to a floating point number
    2: Truncates the decimal postion (time) of the floating point number without changing the value of the integer portion
    3: Converts the floating point number back into a datetime
    This can be used anywhere you'd like.  No need to create a UDF or anything of the sort.  There may be faster ways (cpu cycles) of doing it but the total time you'd save you've wasted on this discussion thread.
     
  • use DATEPART function and retrieve the date parts from the date retrieved.

     

     

    declare @dtTime datetime

    set @dtTime  = getdate()

    SELECT CAST(DATEPART(m, @dtTime)as varchar) +'/'+ CAST(DATEPART(d, @dtTime)as varchar) +'/'+  CAST(DATEPART(yy, @dtTime)as varchar)

    Linto

  • Noeld – please explain why you should not cast a datetime as int.  I have used

    cast(cast(GETDATE() - 0.5 as integer) as DateTime)

    with apparent success.  Just interested ... the FLOOR solution already mentioned seems more elegant anyway. 

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • As I alluded to in my original posting...

    Casting (or Converting) the date as INT or BIGINT may give you the wrong (next day) date because dates with times of 12:00 or later will be rounded up to the next day automatically.  Don't use any form of INT.

    Further, any type of conversion to DECIMAL will cause similar errors due to the automatic rounding inherent in the datatype.

    The idea of a UDF (User Defined Function) is a great one.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Could be the cache had enough data to perform without reads and the cost could go down. I suggest trying multiple times and see what happens. However thare could be other factors, would have to see the execution plan.

  • Thanks for all of your help everyone.

  • I agree with Antares686 suggestion. but one another way for comparision is to use convert function.

    convert(varchar(10),DateTime,101) that will return only date and remove time and you can compare with your another date field.

     

     

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply