Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at the New York ColdFusion User Group (May. 2009) with: Abraham Lloyd and Peter Bell and Gert Franz

Getting Only The Date Part Of A Date/Time Stamp in SQL Server (Revisited)

By Ben Nadel on
Tags: SQL

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:

547 ms
578 ms
516 ms
578 ms
578 ms
531 ms
594 ms
532 ms
578 ms
594 ms

Average: 562.6 ms

DATEADD() / DATEDIFF() Method:

594 ms
531 ms
579 ms
547 ms
594 ms
594 ms
531 ms
593 ms
547 ms
578 ms

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.




Reader Comments

Ben,

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

Reply to this Comment

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))

Reply to this Comment

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#">
SELECT
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)
</cfquery>

You can get different date formats by using codes other than 101.

Here is the link to MS Books On Line for convert:

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Gus

Reply to this Comment

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%

Reply to this Comment

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!

Reply to this Comment

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.

Reply to this Comment

@Jason,

That is an excellent tip. I have not dealt with a lot of UDFs in SQL, so this is very good to know.

Reply to this Comment

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.

Oracle Example:
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.

Reply to this Comment

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'
select @mydate

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 @mydate

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

You could also try
SELECT CAST(CAST(@testdate AS INT) - 1 AS DATETIME)
INstead of using float, no need to use floor then

Reply to this Comment

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

Reply to this Comment

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
where ...
Thx for the Blog,
-Jon-

Reply to this Comment

@Jon,

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.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.