December 28, 2003 at 11:18 pm
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
December 29, 2003 at 3:25 am
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
December 29, 2003 at 10:50 pm
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
December 29, 2003 at 11:57 pm
how about this ?
update Mytable
set DateCol = Cast(datetime as varchar(11))
December 30, 2003 at 2:06 am
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
December 30, 2003 at 2:13 am
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
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]
December 30, 2003 at 2:33 am
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
December 30, 2003 at 4:09 am
But anyhow! Yukon, once available, will have (Endlich!) a separate date and time datatype.
Bye
Gabor
Bye
Gabor
December 30, 2003 at 2:35 pm
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
December 30, 2003 at 3:02 pm
quote:
Nicht jeder weiss, was "Endlich!" bedeutet...
Also, ich habe das ohne Probleme verstanden
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 30, 2003 at 3:12 pm
Ich habe gedacht das ist geheim ist
Once you understand the BITs, all the pieces come together
December 30, 2003 at 3:18 pm
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
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 30, 2003 at 3:28 pm
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
December 30, 2003 at 3:40 pm
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
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 3, 2004 at 4:53 am
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