Getting Only the Date Part of a Date/Time Stamp in SQL Server : Kinky Solutions : A Student's Perspective by Ben Nadel

Recent Entries Complete Entry List August 2008 July 2008 June 2008 May 2008 April 2008 March 2008 February 2008 January 2008 December 2007 November 2007 October 2007 September 2007 August 2007 July 2007 June 2007 May 2007 April 2007 March 2007 February 2007 January 2007 December 2006 November 2006 October 2006 September 2006 August 2006 July 2006 June 2006 May 2006 April 2006 AJAX (14) Ask Ben (103) Books (14) ColdFusion (914) Exercise List (17) Flash (3) Health / Fitness (34) HTML / CSS (29) Javascript / DHTML (91) Life (68) Movies (23) Project HUGE (28) Relationships (7) Search Engine Optimization (14) Skin Spider (30) SQL (83) Work (105) XStandard WYSIWYG (21) RSS 2.0 Custom RSS 2.0

Posted July 7, 2006 at 6:05 PM

Tags: SQL

I got a question the other about getting the date-only part of date/time stamp in SQL server. In my experience there are two ways to go about doing this. Both require casting data types from one type to another. The first example is how I used to go about performing this task before I understood how dates were represented numerically. The second method is related to my understanding of ColdFusion dates and timespans and fortunately transferred well to SQL.

First, the more verbose method. This requires breaking the date into its year, month, and day parts, putting them together in "yyyy/mm/dd" format, then casting that back to a date. This method involves 7 method calls including the final CAST(), not to mention string concatenation (which we all know is wicked slow).

 Launch code in new window » Download code as text file »

The second method is much much cleaner. It uses only 3 method calls including the final CAST() and performs no string concatenation, which is an automatic plus. Furthermore, there are no huge type casts here. If you can imagine that Date/Time stamps can be represented, then converting from dates to numbers and back to dates is a fairly easy process (hopefully).

 Launch code in new window » Download code as text file »

In the example above, we are converting the date/time stamp to its float form. So today (when this was posted), this would yield something like 38903.745537114199. Days are not fractional, meaning that decimal places represent fractions of a day (in hours, minutes, seconds). Then, in order to get the days part (trim off the time portion of the date/time stamp) we are FLOOR()'ing the float value. This will give us the numeric representation of the DAY-only date. Then, we simply cast that back to DATETIME format and there you have it, a date-only date/time stamp.

If you want to compare, try running this:

 Launch code in new window » Download code as text file »

As far as performance is concerned, there is no big surprise here. The second method has fewer function calls, no string concatenation, and in my opinion is a much more natural casting idea. In my testing on a table with several thousand records, the second method generally executed in a fraction of the time that string concatenation method executed.

On 3,000 rows (based on CFTimer execution time):

Average time of method one: 115ms

Average time of method two: 16ms (HUGE performance increase)

Additionally, in testing, method one has some runs that were all over the place; very large execution times. Method two, on the other hand, consisently performed at the same speed, give or take a few ms.

Download Code Snippet ZIP File

Comments (71)  |  Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader Comments

I'm just wondering, can't you simply do a DATE(dateTimeStamp) to get the date part of a date/time stamp field?

Posted by sean on Jul 27, 2006 at 2:32 AM

Sean, that might work in some datebases, but it does not work in mine (running SQL Server 2000... I think)

Posted by Ben Nadel on Jul 27, 2006 at 7:37 AM

The best way to go about this problem in MSSQL is CAST(DATEADD(DAY,0,GETDATE()) AS DATETIME)
Actually, what we want to do is THE_DATE BINARYAND FFFFFFFF00000000

Posted by X on Aug 11, 2006 at 11:01 AM

I am using MS SQL 2005 Server
I tried BEN's way and the other 'best' way = CAST(DATEADD(DAY, 0, GETDATE()) AS DATETIME)

But none of them works. I am developing a VB .NET application. I think I have to use the

.SubString(0, .IndexOf(" ")

function to get the DATE only part. It's slow, but haven't found any other way yet. Thanks all.

Posted by Mehdi Anis on Aug 15, 2006 at 11:01 AM

Mehdi in VB.NET you can try following:-
1. DateTime.Today
2. DateTime.Date
The above will return a DateTime object withe value set to the today's date and time part set to 00:00:00.

BUT if you need to display without the the time part then use:-

DateTime.Today.ToString("yyyy/MM/dd") or
DateTime.Date.ToString("yyyy/MM/dd")

You can change the format as you like; check out the MSDN article for DateTime.ToString method.

Posted by Rajeev Datta on Aug 15, 2006 at 12:06 PM

Rajeev Datta,

Thanks for jumping in with the .NET help. It's been some time since I did anything in .Net and would not be able to offer any useful help.

Posted by Ben Nadel on Aug 16, 2006 at 7:50 AM

thankx 4 such a neat idea.
it really works with highest efficiency.

Posted by partha on Aug 19, 2006 at 4:16 AM

I tried CAST(DATEADD(DAY,0,GETDATE()) AS DATETIME) but the time values are not set to 00:00:00 (using SQL Server 2000).

Posted by Doug on Sep 8, 2006 at 3:17 PM

Doug,

The problem is that you are not doing anything to the original date/time object to get rid of the time. If you want to get rid of the time, you are going to have to round out the date:

CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME )

Doing what you did merely casts the date/time to a date/time without any real manipulation.

Posted by Ben Nadel on Sep 8, 2006 at 3:23 PM

If you are using any flavor of MS SQL server, it seems like you are making this problem way more difficult than it needs to be.

It has been my experience that when most people ask for this they want to use it for display to a user or in some report, so you can safely convert to a string representation. The storage of a DATETIME in SQL will always result in a date and time value (default 00:00) whether or not you pass the time, so removing it prior to storage has no effect.

The built-in CONVERT function, however, allows you to take any date format and return a string representation of just the date portion thusly:

SELECT CONVERT(nvarchar(20), GETDATE(), 101) AS JustTheDate

The Syntax:

CONVERT([datatype], [date value], {format code})

There are several 'format codes' used in MS SQL to pull out parts of the date for your locale and are documented on MSDN. A few examples are: 103 = dd/mm/yy77, 107 = Mon dd, yyyy or 112 = yyyymmdd.

Posted by Garth on Sep 22, 2006 at 2:18 AM

Garth,

I would argue that if people wanted to use a date-only representation for display purposes in a report or what not, they would not even bother manipulating it in SQL. Most any language has some sort of DateFormat() method that can be used on output.

What I have to deal with a lot of the time is date comparisons within SQL that would be messed up if time were compared.

Posted by Ben Nadel on Sep 22, 2006 at 7:37 AM

Very helpful, Ben. I, too, needed just the date within SQL.

I tried CASTing as INT and it seems to work fine. Saves from using FLOOR().

Thanks again - Ben

Posted by (Another) Ben on Sep 26, 2006 at 10:22 AM

Ben,

Nicely done with the casting to INT. I can't believe I never thought of that. I am so used to thinking of date/times as FLOAT that I never considered INT.

Well done... and nice name ;)

Posted by Ben Nadel on Sep 26, 2006 at 5:36 PM

Ben,

I realized why I don't cast to INT. Check here:

http://bennadel.com/index.cfm?dax=blog:296.view

Posted by Ben Nadel on Sep 27, 2006 at 3:10 PM

OK these methods are all fine, but there is one overlooked problem. I wrote a function called DateOnly that encapsulates this time stripping functionality so I don't have to do it over and over.

The other nice thing about a function is it can be used to add a calculated column to a table so you can have one column, let's say OrderDate, that stores the actual date and time, but put a calculated column, call it OrderDateOnly, right next to it, so you can query that column with a regular equals comparison instead of doing a date range.

ALTER TABLE Orders
ADD OrderDateOnly as dbo.DateOnly(OrderDate)

The problem with that is that because it's doing a calculation on a column, it would not be able to use an index on OrderDate to do this query and it would result in a table scan. However, calculated columns may be indexed just like stored columns, but only if the column is deterministic. This is a property that guarantees the function always returns the same value for a given set of parameters. The problem with any solution that uses Convert or Cast is that it makes the function non-deterministic, so a create index would not work.

CREATE INDEX ixOrders_OrderDateOnly on Orders(OrderDateOnly)

The only way I have been able to make a function that is deterministic such that it can be indexed requires the use of the DateAdd function to strip off the hours, minutes, seconds and milliseconds from the date, one at a time. It makes for a messy looking query, but it works.

Here's the final Function.

CREATE FUNCTION DateOnly(@date)
RETURNS datetime
WITH SCHEMABINDING
as
return
DATEADD(ms, -1 DATEPART(ms, @date),
DATEADD(second, -1 DATEPART(second, @date),
DATEADD(minute, -1 DATEPART(hour, @date),
DATEADD(hour, -1 DATEPART(hour, @date), @date)
)))
go

I've not compared this performance wise to the other functions, but I know that in any case where I want to create an indexed calculated column, that alone will outway any other performance issues that might occur on the calculation part alone.

Posted by Joseph Gagliardo on Oct 4, 2006 at 3:27 PM

Joseph,

That is some super high level stuff you have going on there. It is way beyond my understanding of databases but I get the generally idea of what you are doing and why it needs to be done.

Dynamite comment, man, pure dynamite!

Also, moving it into a UDF makes a TON of sense.

Posted by Ben Nadel on Oct 4, 2006 at 4:21 PM

If you're working w/ MSSQL and doing anything with dates, DATEPART is a savior.

Believe me.

Posted by k.dot on Oct 5, 2006 at 10:08 AM

Ok, I beleive in you :: DATEPART is the Savior.

Whatever I do to a DATETIME field, in .NET application it automatically adds 00:00:00 for the time. Ofcourse, we can use client side programming function to convert the DateTime data type to To String() type (From my .NET experience). OR, use DATEPART and create a nvarchar type data on SQL Server side.

My table has "AddedON" as DateTime column. Here is my SQL Select Command:

SELECT FolderName, Convert(nvarchar(2), DATEPART(mm,AddedOn)) + '/' + Convert(nvarchar(2), DATEPART(dd, AddedOn)) + '/' + Convert(nvarchar(4), DATEPART(yyyy, AddedON)) AS AddedOn, (SELECT Count(FolderName) FROM InventoryTable
WHERE InventoryTable.FolderName=NewReleasesTable.FolderName) AS FileCount FROM NewReleasesTable WHERE AddedOn >= (GetDate()-@Range) ORDER BY AddedOn DESC

Another way would be, make whole datetime to a string then split on the blank (seperating date & time).

Here is the result shown:
http://bangla.homeip.net:8000/NewAlbums/index.aspx

Posted by Mehdi Anis on Oct 17, 2006 at 4:11 AM

i have a third party app where all dates are stored as numeric in yyyymmdd format on sql server 2000.

i can't ever do any kind of math on these dates. i try casting and converting to no avail. while i am a novice to begin with, i am a total noob with dates. i would just like to be able use things like DATEADD without getting:

Arithmetic overflow error converting expression to data type datetime.

i've googled my heart out, what can i do?

Posted by bubarooni on Oct 20, 2006 at 6:02 PM

Bubarooni,

I will work out an example for this... let me see what I can come up with. It won't be pretty I am sure, but it might work :)

Posted by Ben Nadel on Oct 20, 2006 at 6:18 PM

Not sure what your problem is. I use that date format all the time. If you convert(datetime, colname) it will return a datetime as long as the data you supply it is proper. Of course, if you are supplying the number as an integer, it will not return the result you expect. You must pass it as string formatted as YYYYMMDD. An integer would be interpreted as that many days since 1900-01-01, which may be the cause of your overflow. I think if you are storing the dates as numerics, you need to cast them first to varchar, then to datetime, like this:

convert(datetime, convert(char(8), colname))

If you still have bad dates, try using the ISDATE function to only convert legal dates and filter out the bad ones.

Posted by Joseph Gagliardo on Oct 20, 2006 at 6:36 PM

The UDF contains a small error. I couldn't create it. I had to replace the '-1' with just a '-'. And then I discovered it always results in a datetime with the time set to '01:00:00'
So it should be:

CREATE FUNCTION DateOnly(@date)
RETURNS datetime
WITH SCHEMABINDING
as
return
DATEADD(ms, - DATEPART(ms, @date),
DATEADD(second, - DATEPART(second, @date),
DATEADD(minute, - DATEPART(minute, @date),
DATEADD(hour, - DATEPART(hour, @date), @date)
)))

Posted by Antoine on Dec 4, 2006 at 6:34 AM

hi,
How can i write a custom type myDate in sql 2005?

-- Create the data type
CREATE TYPE myDate
FROM base_type,,nvarchar (precision,int,25) allow_null,,NULL
I am unsure how to use this. am i looking in the right direction?

Posted by hrub on Dec 21, 2006 at 6:51 AM

Hrub,

Sorry, I do not know anything about custom data types at this time. I will post when I know something.

Posted by Ben Nadel on Dec 21, 2006 at 7:22 AM

I also run into the date/time problem on SQL Server. I have solved the problem in several ways. When possible I simply create the table column with a check statement that prevents time from being added:

create table test
(
dateonly datetime null constraint ckc_dateonly check (DateOnly is null or (dateonly = cast(convert(varchar(8),dateonly,112) as datetime)))
)

When inserting data and you want to use getdate() simply use
cast(convert(varchar(8),getdate(),112) as datetime). (It can be used as the default value for the column also).

I never found a good way around the indexing problem for computed fields (though I do use those also):

create table test
(
DateWithTime datetime null,
DateOnly datetime null constraint ckc_dateonly check (DateOnly is null or (dateonly = cast(convert(varchar(8),dateonly,112) as datetime))),
ComputedDateOnly as (cast(convert(varchar(8),datewithtime,112) as datetime))
)

Of course you can also create a real column "DateOnly" of datetime and have it updated via a trigger when ever an insert/update is done that changes the associated base column. This would be like a computed column but you could then index it however this is always the chance of something getting out of whack.

Posted by James Knowlton on Jan 4, 2007 at 5:14 PM

Sorry I have not been following this thread for a while. Somehow when I posted the source code of the UDF the stars disappeared. It is supposed to be -1 * in there, but as the subsequent comment shows a plain minus sign works well also. In any event, this is the only form of a date correction function I have ever been able to create that will actually be deterministic, such that it is indexable. Using this function to create a date only calculated column is by far the most efficient method of making an index seek on the datetime column. The use of a secondary physical column which is maintained through triggers is not recommended, as triggers greatly impair performance, and the second physical column increases storage space which might also lead to more data pages being required.

The best method is still to just have an index on the regular datetime column itself and use a range query in the form of:

WHERE datecol >= startdate and datecol < startdate + 1

but this is not intuitive to the casual user creating adhoc reports. So in a table in which I actually want to store the time portion, I would include the calculated column using the UDF and also create an index on the dateonly column if it were going to be a column that is frequently used in a WHERE clause. In cases where I don't want a time actually stored, I would correct it before saving it using the UDF to strip off the time. I would typically do this in the stored procedure that adds the records. Or in an extreme case where I have no other choice, possibly a trigger.

Posted by Joseph Gagliardo on Jan 4, 2007 at 6:10 PM

Hey guys,
Just thought I would post about this. I got around this problem very easily, this is the route for a non-programmer.

declare @mydate datetime SET @mydate = (Getdate())
declare @mymonth varchar(2) set @mymonth = month(@mydate)
declare @myday varchar(2) set @myday = day(@mydate)
declare @myyear varchar(4) set @myyear = year(@mydate)

declare @Totals varchar (20) s
et @Totals = (@mymonth + '/'+ @myday + + '/'+ @myyear)

Then just query with @totals you can play with the datatypes etc, but this is the quick and dirty

Posted by Chuck on Feb 14, 2007 at 4:12 PM

Here is my code. Works okay but the last = statement returns an error. How to I compare the newreportdate and the JustTheDate values. I have tried many maybe to many ways.
Thanks
Landon

SELECT reportdate,worktype, maintby,

CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME )as timeret,
CONVERT(nvarchar(20), GETDATE(), 102) AS JustTheDate,
CONVERT(nvarchar(20), reportdate, 102) AS newreportdate
from workorder
where worktype='pm' and maintby ='em' and newreportdate=JustTheDate

Posted by landon humphrey on Feb 22, 2007 at 3:21 PM

Landon,

It's funny, I only recently learned about the CONVERT() function. I had seen it a few times before, but only actually looked it up a few weeks ago. Looks like a very powerful method!

Posted by Ben Nadel on Feb 22, 2007 at 3:58 PM

Landon,

you can solve your problem with the nested select:

select *
from
(SELECT reportdate,worktype, maintby,
CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME )as timeret,
CONVERT(nvarchar(20), GETDATE(), 102) AS JustTheDate,
CONVERT(nvarchar(20), reportdate, 102) AS newreportdate
from workorder
where worktype='pm' and maintby ='em') _temp
where newreportdate=JustTheDate

Posted by Dimonytch on Feb 27, 2007 at 2:14 PM

Hai Friends,

i have one doubts in SQLSERVER Query,

i need to extract only TIME by using Sql Query.

for eg. 10:30am, 11:30am, 12:30pm.

can you give me solution anyone?

Thanks & best Regards,
Mani

Posted by Subramanian on Feb 28, 2007 at 2:03 AM

try this: (date is 24hr)

declare @MyDate datetime
set @MyDate = getdate()

Select
rtrim(datepart(hh, @MyDate)) +'h'+
Case len (datepart(mi, @MyDate))
when 1 then '0'
when 2 then ''
End +
rtrim(datepart(mi, @MyDate)) +'m'+
rtrim(datepart(ss, @MyDate)) +'s'
as 'Time'

Posted by hrub on Feb 28, 2007 at 7:58 AM

Garth had it right, at least with SQL Server. You don't need a custom function to do what's already built in.

If you need to use it in a comparison you can easily do something like

WHERE CONVERT(nvarchar(20), MyDateColumn, 101) BETWEEN @DateFrom AND @DateTo

I always forget the syntax though and have to look it up again...lol

Posted by Andrew on Apr 2, 2007 at 2:20 PM

I know I'm late to the party, but having dealt with both SQL Server and Oracle at length, it's easy to say that date handling and manipulation in SQL Server leaves much to be desired. As evidenced by this thread, truncating dates in SQL Server oftentimes involves massive amounts of function nesting and string concatenation. Oracle offers a function named "trunc", which does the same thing in a neat little package. The group I work for oftentimes deals with massive amounts of data, so we are all the time summarizing that data by day/month/quarter or even year in some cases. Remembering all the hoops to go through is just a mess, so I wrote a SQL Server equivalent to Oracle's TRUNC function.

The function works like this:

fn_trunc(p_date,period)

For instance, if I want to truncate getdate() to today's date:
select fn_trunc(getdate(),'d')

or the first day of this month:

select fn_trunc(getdate(),'m')

.. and so on. fn_trunc will truncate any date to the nearest minute, hour, day, month, quarter or year. The period argument accepts the same arguments the SQL Server DATEPART function uses, as well as the equivalent arguments used in the Oracle TRUNC function.

I'll paste the code below, and please note that this implementation is for a system-wide function. Also note that I have not exhaustively tested the performance of this function call, so please anyone chime in if you have discovered a quicker way to skin this cat. I stayed away from string concatenation where possible, but prefer that over things like multiple DATEPART calls.

<code>
USE master
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE uid = USER_ID('system_function_schema')
AND name = 'fn_trunc')
DROP FUNCTION system_function_schema.fn_trunc
GO

/* ALL SYSTEM-WIDE FUNCTIONS MUST BEGIN WITH "fn_""AND
MUST BE ALL lowercase
*/
CREATE FUNCTION system_function_schema.fn_trunc
(
@p_date datetime,
@p_period varchar(4) = 'd'
)
returns smalldatetime
as
begin
declare @l_date smalldatetime

set @l_date =
case
when @p_period in ('n','mi')
then cast(convert(varchar(16),@p_date,120) as smalldatetime)
when @p_period in ('h','hh')
then cast(convert(varchar(14),@p_date,120)+'00' as smalldatetime)
when @p_period in ('d','dd')
then cast(cast(@p_date as varchar(11)) as smalldatetime)
when @p_period in ('w','wk','ww')
then dateadd(dd, 1 - datepart(dw, convert(varchar(10), @p_date, 111)),convert(varchar(10), @p_date, 111))
when @p_period in ('m','mm')
then cast(convert(char(6),@p_date,112)+'01' as smalldatetime)
when @p_period in ('q','qq')
then cast(cast(year(@p_date) as varchar(4)) + '/' + cast(datepart(q, @p_date) * 3 - 2 as varchar(2)) + '/01' as smalldatetime)
when @p_period in ('y','yy','yyyy')
then cast(convert(char(4),@p_date,120)+'-01-01' as smalldatetime)
else
cast(cast(@p_date as varchar(11)) as smalldatetime)
end

return @l_date
end
go

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
</code>

Posted by Kerr on May 17, 2007 at 2:38 PM

I still don't know what Floor is, but Floor/Cast worked for me.

Thanks, Ben!

Posted by Amber on May 23, 2007 at 1:52 PM

@Amber,

Floor() simply rounds down to the closest integer that is smaller than then number:

Floor( 3.3 ) to 3
Floor( 4.9 ) to 4
Floor( -3.2 ) to -4

And Cast() converts one data type to another.

@Kerr,

That is a pretty bad-ass function you got going there. Thanks for sharing.

Posted by Ben Nadel on May 23, 2007 at 2:28 PM

yikes, use this, and drop all that cast and floor stuff.

create function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end
go

reference:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Posted by APeterson on Sep 20, 2007 at 9:24 PM

@APeterson,

Interesting tip. It took me a minute to understand how that was even working - the zero-date usage was throwing me off. It looks like these two methods are at a similar performance, but your was slightly outperforms the CAST methodologies. Thanks! Every millisecond counts!

Posted by Ben Nadel on Sep 21, 2007 at 7:32 AM

@APeterson,

Actually, after running it a bunch of times, the two different methodologies look to be just about the same. If anything, the Cast() / Floor() way seems a hair faster:

http://www.bennadel.com/index.cfm?dax=blog:963.view

Posted by Ben Nadel on Sep 21, 2007 at 8:06 AM

These methods may be more elegant or perform faster but my original formula of subtracting the hours minutes and seconds is still the only one that will result in a deterministic function that can be used to create a calculated column which is indexable. I cannot conceive of a situation where you would use this function so many times that millisecond differences in performance matter except if you are using it to query from a large table. If that is the case, the performance would be best if the table were already indexed with the calculated value. The original premise of the function was to enable people to write a query to find all records that occur on a date when the datetime field has both date and time.

SELECT * FROM table WHERE date >= date1 and date < DateAdd(day, 1, date1) is the best option in the absence of the DateOnly function, but a casual user would not know to do this.
SELECT * FROM table WHERE dateonly = date1, where dateonly is a calculated column in the table and indexed, would give about equal performance to the first query, but is simple enough that a user of Crystal Reports
would intuitively use it.

I agree that performance is extremely important and that is why I spent days finding the best formula, not just based on an isolated unit test of performance, but on the totality of its use.

I would love to find another solution to this function that would result in ObjectProperty('DateOnly', 'IsDeterministic') to be true, but I have not yet found one. Any function that uses conversion to another datatype loses determinism.

Since you would only have to write this function once, even though it's ugly, it still makes the most sense to use the method of removing the hours, minutes and seconds through DateAdd and DateDiff.

Posted by Joseph Gagliardo on Sep 21, 2007 at 10:14 AM

@Joseph,

I think you have way more DB experience than I do, so I cannot argue one way or the other. I am not even sure what Deterministic means :(

Posted by Ben Nadel on Sep 21, 2007 at 5:52 PM

I don't mean to be argumentative, I just want to share with everyone something that I found to be one of the most difficult problems I've ever had to solve in SQL Server. Determinism is basically a property that says a function will always return the exact same value for a given set of parameters. It is essential that this be true in order to build an index on a calculated column. UDF's that involve rounding or data conversion are generally not deterministic. This DateOnly function was a particularly difficult one to make deterministic. I tried dozens of different formulae (pretty much every other version you see listed here on the site and then some). Ultimately the only one I have found is the one involving subtracting the hours, minutes and seconds. I shared it with everyone because it was so frustrating to get to that solution.

I believe performance is always important, but as I said, unit test performance on the various formulae in isolation is not a true test of the overall practical performance. Ultimately having an indexed column to retrieve the matching rows is the fastest technique. Indexing on the base datetime column and doing a range query is the most efficient because it does not involve the use of the function at all. But because of the complexity of doing the range query properly, the casual SQL user will often get the wrong result or write an inefficient function of their own to fetch rows that happen on a day when using a datetime column that has a time portion in it. So the genesis of this function was to simplify that for them. If you are not concerned with storing the time in the field and simply want to fix the datetime column to midnight by using a default on the column, then I would argue it would be better to use one of the functions that calculates the fixed datetime value the quickest, since determinism would not be necessary for that function. Ideally if I had to do both of these scenarios, I would probably opt to have two different implementations of this function DateOnlyD (deterministic) for the one I use for calculated columns that will be indexed, and DateOnly for ones that I would use for Default constraints or any other case that determinism is not needed for indexing.

Posted by Joseph Gagliardo on Sep 21, 2007 at 6:22 PM

@Joseph,

I think I am understanding a little bit more of what you are saying. I am only recently new to Indexing anything, let alone a calculated column. I have a long ways to go before I can really master this whole SQL language.

Posted by Ben Nadel on Sep 23, 2007 at 11:54 AM

ps. "I don't mean to be argumentative"

... I don't think any of us would learn anything if people didn't take the time to be argumentative! Working in a vacuum is never a good thing, so thanks for helping us to set aside our held beliefs and question what we know :)

Posted by Ben Nadel on Sep 23, 2007 at 11:56 AM

A Great post but on trying to implement it on SQL Compact 3.1 CAST is not supported but you pointed me in the right direction of

CONVERT (datetime, FLOOR(CONVERT (float, GETDATE())))

(.NET (Bracket (Matching (Optional)

Posted by John D. Wilson on Sep 25, 2007 at 9:41 AM

Hi, this question is for Joseph Gagliardo.

I am trying to write a deterministic function in sql server 2005. I intend to use it in a persisted computed column.

The functions I am working on convert RGB color values to HSV and to Hex. However, this is sort of beside the point.

None of my attempts have turned out to be deterministic. I've been creating all these test functions to try to figure where "the line" is for making a deterministic function. Even this:

CREATE FUNCTION TestFunc
()
RETURNS int
AS
BEGIN
RETURN 9;
END

SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.TestFunc'), 'IsDeterministic');

is giving me zero!

What am I doing wrong here?

Posted by Jan Driscoll on Sep 25, 2007 at 6:18 PM

I cannot remember all the details off the top of my head, and I am not near a SQL Server at the moment. But I remember there being a bunch of requirements to make a function deterministic. I think it had to be schema bound, and there were a few environment settings that had to be a certain way, like QUOTED_IDENTIFIER and stuff like that. I am sure you can find some documentation on the requirements for it in books on line or the web, if not, I will try to dig it up in my notes. It's one of those things I do so infrequently I forget some of the details.

I know it is very particular though, and using things like convert and floating points and stuff like that don't work. Actually as I write this you are making me think that I might need to test the function that used the datediff because the quick test I did may have not been deterministic because I might have forgotten some of those requirements. I would be absolutely thrilled if I could find a formula that is easier than the one I've been using but is also deterministic.

Thanks for making me think about this, I will follow up on it tomorrow and let you know what I find.

Posted by Joseph Gagliardo on Sep 25, 2007 at 8:55 PM

Awesome! Thanks a tonn. I generally tend to do this kinda things in app layer c# etc.. but sometimes there is no choice but to use tsql. Saved me quite a bit of time.

Posted by One More Time on Oct 3, 2007 at 9:56 PM

Yup, it was the schema binding! I added WITH SCHEMABINDING and it made all my functions deterministic.

In SQL 2005, using floats makes the function non-precise. That is to say, the IsPrecise property will be 0. However, a function can be non-precise but still be deterministic! However, I'm not sure if precision affects determinism in older versions of SQL server.

Posted by Jan Driscoll on Oct 3, 2007 at 10:12 PM

Nice. Clear, Easy and suggestions

Posted by Roberto Barrantes on Nov 29, 2007 at 11:16 AM

I have another simple solution for separating out time from the datetime stamp.

convert(datetime,( convert(varchar (11),getDate())))

Posted by Girish on Feb 1, 2008 at 12:22 AM

Thank You .....

Posted by Praveen on Feb 5, 2008 at 7:17 AM

To strip time off of datetime values to store only the day, I usually just use this approach

declare @DateValue datetime
set @DateTest = getutcdate()
select convert(datetime, convert(int, @DateTest))
- or -
convert(datetime, convert(int, <DateColumn>))

To store only the time, it is a little trickier, but not much:

declare @DateValue datetime
set @DateTest = getutcdate()

select convert(datetime, convert(decimal(19,12), @DateValue) - convert(int, @DateValue) + 1)
- or -
select convert(datetime, convert(decimal(19,12), <DateColumn>) - convert(int, <DateColumn>) + 1)

Note: The decimal(19,12) gives you the full valid range of the DateTime datatype without any loss of precision. If you don't need that level of precision, you could go less.

-T.

Posted by T. Phaneuf on Feb 6, 2008 at 11:34 PM

Joseph,

You wrote last fall:

>> I know it is very particular though, and using things like convert and floating points and stuff like that don't work. ... I would be absolutely thrilled if I could find a formula that is easier than the one I've been using but is also deterministic.

cast(floor(cast(MyDateField as float) as datetime)

in converting a date to a floating point puts the time in the decimal portion. "Floor" strips away the decimal portion, and the final cast converts that to a datetime with a time of midnight, since the decimal portion of the number had been set to zero. This is deterministic, and in my experience HAS always worked.

I think that it is also probably about as efficient as you can get, although

dateadd(day,0,datediff(day,0,MyDateField))

may under the covers perform the same operations.

Posted by René Valencourt on Feb 28, 2008 at 3:42 PM

Thank you thankyou thankyou. What a pain in the butt. I had to get the invoices sent between 2 specific dates and the hours were really slowing things down... expecially the conversion back and forth for the criteria.

I actually change the structure of the view I was using to draw data from in my web report to:

select clientname, Invoice_id,
CAST(
FLOOR( CAST( invoice_date AS FLOAT ) )
AS DATETIME
) as invoice_date, .......

Then I could do my select statement:

"where ( rs.invoice_date between '" & begin_date & "' and '" & end_date & "') and office_id = " & office_id & " order by office_number, invoice_id "

and will later put it into a sp.

It increased my time by quite a lot. I was practically read to add a new column to the datatase! Converting to varchar and then back to a date and then running the "between" statement was simply awful.

So, thanks again. Very original and simple.

Posted by Dan M. on Mar 24, 2008 at 4:50 PM

Oops, I just noticed a typo in my post from February 28...

I posted

cast(floor(cast(MyDateField as float) as datetime)

when I meant

cast(floor(cast(MyDateField as float)) as datetime)

Posted by René Valencourt on Mar 24, 2008 at 5:03 PM

Thanks for this information on how to get the date part only out of the date/time stamp, I looked everywhere for this and you r solution is the only one that worked.

Posted by Dominic Lallemant on Apr 3, 2008 at 11:57 PM

Last September Joseph wrote:

>> Determinism is basically a property that says a function will always return the exact same value for a given set of parameters. It is essential that this be true in order to build an index on a calculated column. UDFs that involve rounding or data conversion are generally not deterministic.

In February I wrote:

>> "cast(floor(cast(MyDateField as float)) as datetime)", in converting a date to a floating point, puts the time in the decimal portion. "Floor" strips away the decimal portion, and the final cast converts that to a datetime with a time of midnight, since the decimal portion of the number had been set to zero. This is deterministic, and in my experience HAS always worked.

I must retract and eat my words... :( It does not work if you actually attempt to create an index. I spoke from a theoretical point of view, not from a SQL Server point of view.

SQL Server does not analyze what you are actually doing, to determine whether or not the results should be deterministic (as in my example above). It just makes a blanket a priori determination that if you use the cast/convert operation on a date / float, that the formula will not be deterministic, even if you can demonstrate mathematically for your particular operation that it would be deterministic, as it would for what I showed above, or for this one:

cast( cast(cast(MyDate as float) as int) as datetime)

The definition of "deterministic" that Joseph provides -- always returning the same value for the same set of parameters -- is essentially the one that Microsoft uses, but does not implement precisely.

Thank you Joseph for your in-depth analysis and contribution.

René

Posted by R A Valencourt on Apr 15, 2008 at 9:33 AM

I believe Garth had the correct answer. All you need to use is the correct format code. Perfect!

Posted by Oliver on May 13, 2008 at 10:41 AM

I've read a lot of comments and the main pieces of advice I have are:
- Avoid strings, they're slow
- Some operations take longer than others
- 2 quick operations can be faster than 1 slow operation
- Although "an integer can be used as a date" the database engine still has to do an 'implicit' CAST or CONVERT
- Usining implicit CAST or CONVERT saves typing, not processing time

The fastest answer I have seen is the following:
SELECT
DATEADD
(
DAY,
DATEDIFF(DAY, 0, @date),
0
)

Although using INT or FLOOR to remove the timepart was nearly as fast in my experience.

Getting the Time only is similar:
SELECT
DATEADD
(
DAY,
-DATEDIFF(DAY, 0, @date),
@date
)

Mat.

Posted by MatB on May 15, 2008 at 8:55 AM

WOW - That's the slickest version yet

I always used to use the CONVERT(SmallDateTime, FLOOR(CAST(@dtDate as float)), 103) method

The CAST as INT would also work (because of the FLOOR() function)

but your DateAdd / DateDiff method not only runs quick, is fully Deterministic, can work with both date and time parts, and as a bonus takes up less space on screen ;-)

Thanks

P.S. This has been one of the best SQL blogs I've read in a while - Thanks all.

Posted by Rem on Jun 3, 2008 at 8:01 AM

I agree. That is the slickest solution.

I actually just added it to my little toolbox of functions.

CREATE FUNCTION fnShortDate(@date datetime) RETURNS datetime
AS
BEGIN

declare @dateback datetime

SELECT @dateback=DATEADD(DAY,DATEDIFF(DAY, 0, @date),0)

return @dateback
END
GO

so now I can just use:

select dbo.fnShortDate(getdate())

just an excellent solution, thanks very much.

Posted by Dan M. on Jun 3, 2008 at 3:26 PM

FWIW, I have found in my testing two things:

<ul>
<li>Any data type conversion is much slower than the dateadd method.</li>
<li>Encasing this functionality inside a function (see my trunc function above) results in much slower processing as opposed to just writing out the whole messy dateadd stuff every single time.
</ul>

As such, I have abandoned use of my trunc function. Even though it saves a boatload of redundant syntax, the speed difference is insurmountable when running any sort of date truncation on large tables.

Did I mention that I hate SQL Server's date handling methods?

YMMV

Posted by haylo75 on Jun 3, 2008 at 3:33 PM

Hmm, thought I could use unordered lists here... my bad!

Posted by haylo75 on Jun 3, 2008 at 3:34 PM

Thanks its very good 1

Posted by karan on Jun 4, 2008 at 11:28 AM

You can use SELECT CONVERT(CHAR(10),GETDATE(),103) from

TABLENAME

You will get month/day/year format. If you want different format change 103 to 101 or some other from 101 to 113

Posted by Sunilkumar on Jun 6, 2008 at 7:23 AM

You can also cast to binary, and rely (which is what you do when using float, only there you do it implicitly) on SQL Server's spec that defines it that datetime stores the date in the first 4 bytes and time in the last 4. Casting to binary(4) truncates the higher bytes (date) so we get the time only. Moreover, the same method it is applicable with smalldatetime, only casting to binary(2) (trying to use real instead of float to use with datetime won't work because real is signed but smalldatetime is unsigned).

Hope you like this idea. If you have a better name for these functions I would be glad to hear. Here is sample code which creates user-defined functions and tests them a bit.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_OnlyDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_OnlyDate]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_OnlyTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_OnlyTime]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_OnlySmallDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_OnlySmallDate]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_OnlySmallTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_OnlySmallTime]

CREATE FUNCTION fn_OnlyDate(@dt AS datetime)
RETURNS datetime
AS
BEGIN
RETURN @dt - CAST(CAST(@dt AS binary(4)) AS datetime)
END
GO

CREATE FUNCTION fn_OnlyTime(@dt AS datetime)
RETURNS datetime
AS
BEGIN
RETURN CAST(CAST(@dt AS binary(4)) AS datetime)
END
GO

CREATE FUNCTION fn_OnlySmallDate(@sdt AS smalldatetime)
RETURNS smalldatetime
AS
BEGIN
RETURN @sdt - CAST(CAST(@sdt AS binary(2)) AS smalldatetime)
END
GO

CREATE FUNCTION fn_OnlySmallTime(@sdt AS smalldatetime)
RETURNS smalldatetime
AS
BEGIN
RETURN CAST(CAST(@sdt AS binary(2)) AS smalldatetime)
END
GO

-- Check one datetime, smalldatetime and negative datetime
DECLARE @dt AS datetime, @sdt AS smalldatetime
SET @dt = RAND() * 65535
SET @sdt = @dt
SELECT @dt [Original DateTime], dbo.fn_OnlyDate(@dt) [Only Date], dbo.fn_OnlyTime(@dt) [Only Time]
SELECT @sdt [Original SmallDateTime], dbo.fn_OnlySmallDate(@sdt) [Only Date], dbo.fn_OnlySmallTime(@sdt) [Only Time]
SET @dt = -RAND() * 53650
SELECT @dt [Original DateTime (Negative)], dbo.fn_OnlyDate(@dt) [Only Date], dbo.fn_OnlyTime(@dt) [Only Time]

-- Check integrity
DECLARE @i int, @errors int
SET @i = 0
SET @errors = 0
WHILE (@i < 1000000)
BEGIN
SET @dt = RAND() * 65535
SET @sdt = @dt
IF (@dt <> ( dbo.fn_OnlyDate(@dt) + dbo.fn_OnlyTime(@dt) ))
SET @errors = @errors + 1
IF (@sdt <> ( dbo.fn_OnlySmallDate(@sdt) + dbo.fn_OnlySmallTime(@sdt) ))
SET @errors = @errors + 1
SET @i = @i + 1
END
PRINT @errors

Posted by Yuval Sadan on Jul 3, 2008 at 2:48 AM

Easy solution ...

CONVERT(DATETIME, CONVERT(VARCHAR, getdate(), 111)))

Posted by Nicholas Brus on Aug 8, 2008 at 4:14 AM

Also ...

1. Date & Time truncated to date ...

CAST(CONVERT(VARCHAR, getdate(), 111) AS DATETIME)

'2008-07-18 14:15:29.000' -> '2008-07-18 00:00:00.000'

2. Date & Time truncated to minutes ...

CAST(LEFT(CONVERT(VARCHAR, getdate(), 120), 16) AS DATETIME)

'2008-07-18 14:15:29.000' -> '2008-07-18 14:15:00.000'

Posted by Nicholas Brus on Aug 8, 2008 at 5:00 AM

Great stuff; please subscribe me.

Thanks.

Posted by Doug D. on Aug 8, 2008 at 10:53 AM

INSERT INTO #date (Date) values (GetDate())
go
SELECT Convert (char (10), GETDATE() , 101)
FROM #date

Posted by k on Aug 20, 2008 at 9:50 AM

Post Comment  |  Ask Ben