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

Posted September 21, 2007 at 8:01 AM by Ben Nadel

Tags: SQL

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

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

http://www.databasejournal.com/features/mssql/print.php/3076421

Cheers,

Davo

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

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.

http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html

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.

@Jason,

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.

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.

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

SELECT CAST(GETDATE() AS date)

Cheers,

Sergy

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 http://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.

http://databases.aspfaq.com/database/what-are-the-valid-styles-for-converting-datetime-to-string.html

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-

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

Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment