Convert getdate() to date only
-
Is this the correct way to convert getdate() to date only?
declare @date as datetime
set @date = CAST(ROUND(CAST(getdate() AS float),0,1) AS datetime)
thanks
-
I've not seen that solution before. It is rather neat
and it should be OK.The way I've been doing it is:
declare @date as datetime
select @date = convert(datetime,convert(varchar(11),getdate()))
Or in 'modern' money...
declare @date as datetime
select @date = CAST(CAST(getdate() as varchar(11)) as datetime)
in all cases the convert/cast back to datetime can be ommitted as SQL will do it anyway and it looks simpler in the code
declare @date as datetime
select @date = convert(varchar(11),getdate())
select @date = CAST(getdate() as varchar(11))
select @date = ROUND(CAST(getdate() AS float),0,1)
I don't know which performs best. Probably not much in it.
-
And this is how I do it:
select cast(floor(cast(getdate() as float)) as datetime)
... same principle as ikorenbl.
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 -
Here's how I do it... runs nasty fast too.
------ Finds the date without the time (Time=00:00:00.000)
SELECT DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))
AS DateOnly
--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) -
Same execution plans for all methods -> use the one that makes most sense to you
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 -
There was an extensive exchange on this a while ago and I think Jeff's solution came out fastest - but I don't remember the float method being considered.
I always get worried about converting dates to character strings and back again in case there is some dateformat setting that breaks it. Of course, if you can guarantee your dateformat then this is not a problem but I still would expect it to be relatively slow.
-
I have a calendar table, with one record for each day since way back and 15 years into the future.
Would it be fast if I joined my datetime field with the calendar table?
Then it should be possible to use indexes for the searching?
-
As always, it depends on what you are doing. In general terms, I would expect Jeff's function to be faster, especially as you would have to join on a range, but the only sure way is to test it and see.
-
Thanks for your replies. All solutions seem to work.
When you use DATEDIFF(dd,0,GETDATE()), what does 0 represent? When does it count from?
-
This works and looks good to me.
declare @date as datetime
select @date = convert(varchar(10), getdate(),101)
print @date
-
Date 0 is 1/1/1900.
WARNING : Visual Basic uses 31 December 1899 as its base so, if you start handling dates as numerics, you can get a mismatch - it caught me once!
-
That's the method I use also, though now I'll look for opportunities to try Jeff's version too (though it's a little more obtuse, perhaps...but then again, I've never been one to worry about being obtuse
Though, now that I test it out...most of the time I still just want the 10-character date (i.e., MM-DD-YYYY) so would still have to convert, or LEFT(), or something to get what I want anyway...suppose the CONVERT is just as easy in that respect.
aka "Paul"
Non est ei similis.Any man who can drive safely while kissing a pretty girl is simply not giving the kiss the attention it deserves. ~ Albert Einstein
-
If you look inside of any any table with datetime field you can see that SQL server uses 4 bytes for smalldatetime (same as real) and 8 bytes for datetime (same as float). And if you try to play with conversion from datetime to float and back you realize that all limitations for maximum and minimum datetime and smalldatetime values come from limitations for real and float with necessary precesion.
So datetime is stored in tables as float. That's why conversion from datetime to float does not require any conversion, same value is interpreted in another way. But conversion from datetime to varchar requires real job from processor. And it takes time.
Try to run this against any big table (100 000 rows or more) you have on your site:
declare @TDate as datetime
select @TDate = convert(datetime, '2004-05-01')
select count(StartDate)
from dbo.Events
where convert(datetime, convert(varchar(10), StartDate,101)) > @TDate
select count(StartDate)
from dbo.Events
where DATEADD(dd,0,DATEDIFF(dd,0,StartDate)) > @TDate
select count(StartDate)
from dbo.Events
where cast(floor(cast(StartDate as float)) as datetime) > @TDate
Run every query separately and see how much time it will take.
I've got 600 000 rows in the table with datetime field, I tested it against different servers (from local on my laptop to real one equiped with RAID) and everywhere 2 last queries took approximately the same time to run, but first one - with varchar - took about 4 times longer.
So, try to avoid conversion of datetime to varchar. it's only reasonable when you need to present the result (for example, transfer data to VB), everywhere else it just slows the server down.
_____________
Code for TallyGenerator -
I've always used CAST(FLOOR(CAST(getdate() AS FLOAT))AS DATETIME)
Watch out if you use round. Floor truncates the floating points so 2004/10/06 16h00 becomes 2004/10/06 00h00.
If however you use round, 2004/10/06 16h00 becomes 2004/10/07 00h00. The decimal portion (which represents the time) is greater than 0.5 and is rounded up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
If you use round(float,0,1), it truncates float to integer (ie 0.5 and above are not rounded up), so the given example actually works OK. Eg
select round(1.999999,0,1) as IntPart
returns 1.000000
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply