Removing Time from Date

  • Hi,

    I need help to remove the time part (hh:mm:ss) from a date column. I'm now using Convert function, but the other day I found a script that use DATEADD & DATEDIFF to efficiently remove the time portion...but I lose that script. I got that script in this site...

    Please help and thanks

    Hendry

  • I still belive that the convert is the most efficient way to remove the time part as it uses the less function calls

    update Mytable

    set DateCol = convert(datetime, convert(char(10), DateCol, 103), 103)

    It is quite fast, easy and straightforward

    Bye

    Gabor



    Bye
    Gabor

  • Gabor, Thanks for your response. However, using Convert function with a defined style will query syslanguages table so there's a bit of I/O hit.

    I got that info from somewhere in this forum but I miss it ...

    Hendry

  • how about this ?

    update Mytable

    set DateCol = Cast(datetime as varchar(11))

  • And what about this:

    update Mytable

    set DateCol = Cast(DateCol as int)

    The idea is the remove the fraction part (hh:mm:ss:nnn) and only keep the integer part (the days)

    Bye

    Gabor



    Bye
    Gabor

  • Gabor, I guess that won't be correct

    
    
    DECLARE @dt datetime
    DECLARE @dn datetime

    set @dt = '30.12.2003 11:00:00'
    set @dn = '30.12.2003 13:00:00'

    SELECT CAST(@dt as int), @dt, cast(@dn as int), @dn


    ----------- ------------------------ ----------- ------------------------
    37983 2003-12-30 11:00:00.000 37984 2003-12-30 13:00:00.000

    (1 row(s) affected)

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    Edited by - Frank Kalis on 12/30/2003 02:13:08 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Frank,

    You are right. I've forgotten this behavior

    I've corrected this:

    update Mytable

    set DateCol = Cast(DateCol-0.5 as int)

    Bye

    Gabor



    Bye
    Gabor

  • But anyhow! Yukon, once available, will have (Endlich!) a separate date and time datatype.

    Bye

    Gabor



    Bye
    Gabor

  • select cast(cast(cast(cast(getdate() as binary(8)) as binary(4)) as binary(8)) as datetime)

    "getdate()" used as an example of a date.

    Cast it to binary(8). Cast that to binary(4) -- that lops off the right 4 bytes, which is where the time component lives. Cast it back to binary(8), which will pad it out with zeroes (hence, zero time).

    This might be faster than the convert function.

    Also, an oldie but goodie from a colleague of mine:

    datediff(day, 0, @someDateTime)

    This will return an int that is the date component of the datetime. If your code path is such that the int can be coerced back to datetime, it's less typing.

    It also will not round up dates after 12 noon (for better or worse).

    Nicht jeder weiss, was "Endlich!" bedeutet...

    Chris Hofland


    Chris Hofland

  • quote:


    Nicht jeder weiss, was "Endlich!" bedeutet...


    Also, ich habe das ohne Probleme verstanden

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Ich habe gedacht das ist geheim ist



    Once you understand the BITs, all the pieces come together

  • Uhoh, it seems that there are more people here that understand some German than I thought. Hm...should be careful with what I say in German

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Meiner Eltern sind von Hagen - Wetter (Ruhr). Ausgevandert in '58.

    Ich wahr oefters in Deutchland auf urlaub und im fuenften Schulyahr.

    My spelling's pretty bad however.

    Herzliche Glueckwunch fur das Nue Yahr!



    Once you understand the BITs, all the pieces come together

  • Not joking?

    The world is really a village. I started working with a company in Gevelsberg, only some kilometres from Hagen. And I still know some guys in Wetter.

    Crazy!

    Anyway, happy new year to you, too.

    Hopefully 2004 will be more peaceful than this year!

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • So what was the answer to this question? Hendry !!

Viewing 15 posts - 1 through 15 (of 15 total)

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