Removing Time from Date

Question

Post reply

Removing Time from Date

Henmi

Ten Centuries

Points: 1015
More actions
December 28, 2003 at 11:18 pm

#160577

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

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

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

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 1

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

Henmi Ten Centuries Points: 1015 More actions · Answer 2

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

edwin_fredrick Old Hand Points: 344 More actions · Answer 3

how about this ?

update Mytable

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

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

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]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

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

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 7

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

Bye

Gabor

Bye
Gabor

sql_servant Old Hand Points: 315 More actions · Answer 8

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