-
truncate the time portion in datetime stamp
Hi all:
first of all, i must say that this website is just awesome...
my question is how do i truncate the time portion in a datetime stamp in a single sql statement.
thanks.
-
Can use datepart function, check books online for details.
-
datepart function
Yes, currently i am using the datepart function to get what I need..I was just wondering if there was a different way to do it.
Thanks
-
Have a look at functions CONVERT and CAST.
e.g. SELECT CONVERT (VARCHAR, GETDATE(), 112)
will give you something like 20030402
Check the other "style" values in books on line to see which is the closest match to your desired result
-
There's also
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
which will also get the job done, and seems to be somewhat marginally faster. Works through side effects of data conversion, however...
-
Originally posted by Wobin
There's also
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
select dateadd(dd, datediff(dd, 0, getdate()), 0)
This should be fast and more accurate than the float conversion.
Bambola.
-
I just had to research this myself as well.
A datetime is essentially the same as a float.
So in a better world
select floor(getdate())
would truncate to midnight.
However, MS in their infinite wisdom does not allow implicit translation between datetime and float.
So you have to convert it, then floor it, then convert it back to a datetime
hence
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
These all do the same thing
convert(datetime,convert(int,getdate()-0.5)),
convert(datetime,floor(convert(float,getdate()))),
cast(cast(getdate()-0.5 as int) as datetime),
cast(floor(cast(getdate() as float)) as datetime)
This isn't a 'side effect' of convert(), it's just manipulation of numbers.
Using the datediff and dateadd method is not more accurate or faster, but some may be more comfortable with it.
I ran some tests and
cast(cast(getdate()-0.5 as int) as datetime)
or
convert(datetime,convert(int,getdate()-0.5))
are 5% faster than using floor()
Using datediff and dateadd takes exactly the same time as the floor() method.
My 6 bits worth.
Ike
-
select left(getdate(),11)
-
that returns a varchar, not a datetime
-
select convert(datetime,left(getdate(),11))
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|