NOTE: See comments section below for some further insight.
I have talked before about taking a SQL server date/time stamp and stripping out the time portion, leaving you with a date-only time stamp. Traditionally, I have done this with a combination of CAST() and FLOOR() method calls to convert the date/time stamp to a float and the rounding down on the number to remove the time portion. It was been working great.
Today, though, APeterson suggested using a new method that uses DATEADD() and DATEDIFF(). Assuming the column in question is "date_created", he suggested doing the following:
DATEADD( DD, 0, DATEDIFF( DD, 0, date_created )
I have to admit, at first, the above statement really confused me; I couldn't see how it was working until I broke it down by parts. Let's take a look at it from the inside out. The inner most statement is:
DATEDIFF( DD, 0, date_created )
This gives us the number of DAYS (DD) that make up the difference between our date (date_created) and the SQL zero date (0). It was the use of this zero date that was really throwing me off, which is a bit embarrassing since I am such a huge proponent of date-math usage in ColdFusion. This DATEDIFF() call gives us the number of days without any time since the "beginning of time".
Then, the next statement takes the above number and adds it back to the zero date:
DATEADD( DD, 0, DATEDIFF( ** ) )
Breaking it apart makes a bit more sense.
Ok, so now that I understand how it's working, it's time to see if this new methodology actually performs better. I am going to run both of these methodologies against a table with 50,000+ records and see which one tends to run faster:
<!--- When selecting the hit information, we are going to get a Date-Only column for the date created using our traditional CAST / FLOOR methdology. ---> <cftimer type="outline" label="Date-Only Using CAST"> <!--- Query for Hit information. ---> <cfquery name="qHit" datasource="#REQUEST.DSN.Source#"> SELECT h.id, h.date_created, ( CAST( FLOOR( CAST( h.date_created AS FLOAT ) ) AS DATETIME ) ) AS date_only_created FROM web_stats_hit h </cfquery> <!--- Output the first record. ---> #qHit.RecordCount#<br /> #qHit.date_created#<br /> #qHit.date_only_created#<br /> </cftimer> <!--- When selecting the hit information, we are going to get a Date-Only column for the date created using our new DATEADD / DATEDIFF methodology. ---> <cftimer type="outline" label="Date-Only Using DATEADD / DATEDIFF"> <!--- Query for Hit information. ---> <cfquery name="qHit" datasource="#REQUEST.DSN.Source#"> SELECT h.id, h.date_created, ( DATEADD( DD, 0, DATEDIFF( DD, 0, h.date_created ) ) ) AS date_only_created FROM web_stats_hit h </cfquery> <!--- Output the first record. ---> #qHit.RecordCount#<br /> #qHit.date_created#<br /> #qHit.date_only_created#<br /> </cftimer>
Running the above code several times, here's what we get the following execution times:
CAST() / FLOOR() Method:
Average: 562.6 ms
DATEADD() / DATEDIFF() Method:
Average: 568.8 ms
Over 50,000+ records, it looks like these methods are neck and neck. It seem the the CAST() / FLOOR() one is perhaps just a hair faster. Since neither jumps out at me as being better, I guess you just have to go with which ever one makes you more comfortable.
if all you want to do is to strip the time off of the datetime, then use convert() as so:
select convert(char(10), mycolumn, 101) from sometable
this will give you back the datetime column in "mm/dd/yyyy" format.
if you want to take it further, you could wrap a cast around it and get it back as a valid timestamp instead of a character as such:
select cast(convert(char(10), mycolumn, 101) as datetime) from sometable
With your original method, you did the conversion using numeric dates instead of strings - not surprising given that you usually like to deal with dates as numbers. ;-)
Do you know if using numeric values is inherently faster than using strings? I've done this before by converting to a varchar and back. I was curious how that would stand up timewise with your same table, using something like the following?
convert(datetime, convert(varchar, h.date_created, 101))
Great, thanks Ben. Lots of other SQL date goodness at this link (not mine):
Tony has it right. The built in Convert function is the way to go here.
If you want to see how many hits you get per day.
<cfquery name="qHit" datasource="#REQUEST.DSN.Source#">
CONVERT(char(10), h.date_created, 101) AS dateOnly
COUNT(h.id) AS hitCount
FROM web_stats_hit h
GROUP BY CONVERT(char(10), h.date_created, 101)
You can get different date formats by using codes other than 101.
Here is the link to MS Books On Line for convert:
We use the convert Char(10) method here too.
CONVERT( DateTime, CONVERT( Char(10), ci_importDate, 101 ) )
I benchmarked the different methods in our database hoping yours might be faster, because we use it for date grouping A LOT for our in house reporting.
Every thing I run shows the two you posted producing identical results, but converting to Char looks to be a around 30% faster.
Comparing the execution plans in our database weighs your methods at 58% with the Char method at 42%
You could take this a step further using the convert and turn it into a udf on sql server to gain more usability.
So I just re-ran my testing, except this time adding the third method of CONVERT(). A few things to note: on 50,000+ records, the CONVERT() method did seem to come in about 40ms faster than the CAST(). However, unlike earlier today, the DATEADD() method seemed slightly faster than the CAST() method.
Earlier today, I was the only one in the office. Now, the whole office is here on the same server, potentially all making calls to the database. This might very unofficially mean that the CAST() method does not hold up as well under heavier load??
CONVERT() also seems to outperform the DATEADD() method as well. This all very surprising to me as I would suspect converting anything to a string would be slower than working with numbers.
Very good to know!
We do have a function for doing this.
However, from just testing it in a group by for the year, involving around 300,000 records, the inline version is about 4x faster.
We use the function for param sets, and in the select clause, but if we're at all concerned about speed, we avoid functions if at all possible.
That is an excellent tip. I have not dealt with a lot of UDFs in SQL, so this is very good to know.
+1 to UDF's in SQL Server being slow. Only use when absolutely necessary.
I can corroborate the statement about UDF encapsulation being slow. SQL Server's date handling is pathetic when compared to Oracle's implementation, so I created a UDF (posted elsewhere on this blog) that mimics the TRUNC function in Oracle. I utilized various nested functions to truncate datetimes to the previous minute, hour, day, week, month, quarter and year. I loved using my UDF because it hid all of the messy work that must be done in SQL Server in order to truncate datetimes. However, after extensive testing I found using SQL Server's native functions in my code was so much faster (in some cases an order of magnitude) that I felt compelled to stop using my UDF, even though doing so results in bloated, ugly unreadable code.
SELECT TRUNC(date_column) FROM table
gets the nearest day from the date_column without altering the data type.
ALL of the various SQL Server methods involve nesting of functions in order to get the requested data back to a datetime data type. I blame the SQL Server programmers for this. I don't know of a SQL Server / Oracle user who feels differently. Date handling and manipulation is so important for many reasons that I simply don't see any excuse for the SQL Server implementation being so obtuse.
One drawback to using the CONVERT function is that it is not language independent - ie: if you need to support French or other language settings for SQL Server these methods will cause problems. To support french and english we created a UDF that returns the following as a datetime:
DATEDIFF(d, 0, @mydate)
If course this can also be done inline, as illustrated below:
declare @mydate datetime
select @mydate = '20080831 12:35'
set language french
--Language-specific - fails for french
--SELECT CONVERT( DateTime, CONVERT( Char(10),@mydate, 101 ))
--Return a datetime in a result set
SELECT CAST(DATEDIFF(d, 0, @mydate) AS DATETIME)
--SET a datetime variable, cast is implicit
SELECT @mydate = DATEDIFF(d, 0, @mydate)
SELECT CAST(GETDATE() AS date)
All of above reading a huge discussion but getting all the date with like
Before :"1984-12-28 01:56:30.000"
and end of execution of all query
End : 1984-12-28 00:00:00.000" that assume s as 12:00AM in calculation
May be it is funny but simply why not you all try only
select left(date_field,11) from table
Try it. feel it. forget it.
but be easy.
cast(cast (column as date)as datetime) worked for me
With regard to the posts which used 'as date' it should be noted that this datatype is SQL 2008 specific. 2005 and older do not support this.
You could also try
SELECT CAST(CAST(@testdate AS INT) - 1 AS DATETIME)
INstead of using float, no need to use floor then
Above comment by me errored rounds up and down above and below midday
SELECT CAST(CAST(@testdate AS INT) - DATEPART ( hh , @testdate ) / 12 AS DATETIME)
Works but not sure it is better than what you have with float
Geoff - I recommend viewing the (very long) string of comments in another related thread at www.bennadel.com/blog/122-getting-only-the-date-part-of-a-date-time-stamp-in-sql-server.htm
Bottom line - I don't use CAST/CONVERT for any action in SQL Server when it's not necessary. I use various iterations of DATEADD/DATEDIFF combinations for date truncation.
This is an excellent post which may solve your all problem regarding datetime conversion.
Ben, not a convertion to extract Date from DateTime, but more a method to not have to do it at all. In a scheduling application I am working on we actualy stored the time as "# of seconds since midnight" in a seperate field, allowing the Date field to allways contain midnight. When we need the date & time together, we use the DateAdd function:
Select DATEADD (ss,JobStartTime , JobStartDate) as JobStartDateTime from Job
Thx for the Blog,
I like that approach; I've done something similar where I'll have the date in one field and a 5-character ("HH:MM") in another field. I pretty much never care what the hours/minutes are, just the date. And, in the few cases where I do need to sort on date/time, I think I just merge the two fields (thought I can't remember off the top of my head).
Your trick with the number of seconds since 12AM seems to take care of that in a much more elegant way, though. Thanks for the tip.