<p>Here is the issue I am having: I have a large query that needs to compare datetimes in the where clause to see if two dates are on the same day.  My current solution, which sucks, is to send the datetimes into a UDF to convert them to midnight of the same day, and then check those dates for equality.  When it comes to the query plan, this is a disaster, as are almost all UDFs in joins or where clauses.  This is one of the only places in my application that I haven't been able to root out the functions and give the query optimizer something it can actually use to locate the best index.</p>

<p>In this case, merging the function code back into the query seems impractical.</p>

<p>I think I am missing something simple here.</p>

<p>Here's the function for reference.</p>

<pre><code>if not exists (select * from dbo.sysobjects 
              where id = object_id(N'dbo.f_MakeDate') and               
              type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
  exec('create function dbo.f_MakeDate() returns int as 
         begin declare @retval int return @retval end')
go

alter function dbo.f_MakeDate
(
    @Day datetime, 
    @Hour int, 
    @Minute int
)
returns datetime
as

/*

Creates a datetime using the year-month-day portion of @Day, and the 
@Hour and @Minute provided

*/

begin

declare @retval datetime
set @retval = cast(
    cast(datepart(m, @Day) as varchar(2)) + 
    '/' + 
    cast(datepart(d, @Day) as varchar(2)) + 
    '/' + 
    cast(datepart(yyyy, @Day) as varchar(4)) + 
    ' ' + 
    cast(@Hour as varchar(2)) + 
    ':' + 
    cast(@Minute as varchar(2)) as datetime)
return @retval
end

go
</code></pre>

<p>To complicate matters, I am joining on time zone tables to check the date against the local time, which could be different for every row:</p>

<pre><code>where 
dbo.f_MakeDate(dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, t.TheDateINeedToCheck), 0, 0) = @activityDateMidnight
</code></pre>

<p>[Edit]</p>

<p>I'm incorporating @Todd's suggestion:</p>

<pre><code>where datediff(day, dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, t.TheDateINeedToCheck), @ActivityDate) = 0
</code></pre>

<p>My misconception about how datediff works (the same day of year in consecutive years yields 366, not 0 as I expected) caused me to waste a lot of effort.</p>

<p>But the query plan didn't change.  I think I need to go back to the drawing board with the whole thing.</p>

[{'answer_id': 22579, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 3, 'selected': False, 'text': '<pre><code>where\nyear(date1) = year(date2)\nand month(date1) = month(date2)\nand day(date1) = day(date2)\n</code></pre>\n'}, {'answer_id': 22592, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 7, 'selected': True, 'text': '<p>This is much more concise:</p>\n\n<pre><code>where \n  datediff(day, date1, date2) = 0\n</code></pre>\n'}, {'answer_id': 22600, 'author': 'AlexCuse', 'author_id': 794, 'author_profile': 'https://Stackoverflow.com/users/794', 'pm_score': 2, 'selected': False, 'text': '<p>this will remove time component from a date for you:  </p>\n\n<pre><code>select dateadd(d, datediff(d, 0, current_timestamp), 0)\n</code></pre>\n'}, {'answer_id': 22640, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 4, 'selected': False, 'text': "<p>You pretty much have to keep the left side of your where clause clean. So, normally, you'd do something like:</p>\n\n<pre><code>WHERE MyDateTime &gt;= @activityDateMidnight \n      AND MyDateTime &lt; (@activityDateMidnight + 1)\n</code></pre>\n\n<p>(Some folks prefer DATEADD(d, 1, @activityDateMidnight) instead - but it's the same thing).</p>\n\n<p>The TimeZone table complicates matter a bit though. It's a little unclear from your snippet, but it looks like t.TheDateInTable is in GMT with a Time Zone identifier, and that you're then adding the offset to compare against @activityDateMidnight - which is in local time. I'm not sure what ds.LocalTimeZone is, though.</p>\n\n<p>If that's the case, then you need to get @activityDateMidnight into GMT instead.</p>\n"}, {'answer_id': 22655, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 2, 'selected': False, 'text': '<p>Make sure to read <a href="http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/only-in-a-database-can-you-get-1000-impr" rel="nofollow noreferrer">Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code</a> so that you are sure that the optimizer can utilize the index effectively when messing with dates</p>\n'}, {'answer_id': 22656, 'author': 'Rad', 'author_id': 1349, 'author_profile': 'https://Stackoverflow.com/users/1349', 'pm_score': 1, 'selected': False, 'text': "<p>You're spoilt for choice in terms of options here. If you are using Sybase or SQL Server 2008 you can create variables of type date and assign them your datetime values. The database engine gets rid of the time for you. Here's a quick and dirty test to illustrate (Code is in Sybase dialect):</p>\n\n<pre><code>declare @date1 date\ndeclare @date2 date\nset @date1='2008-1-1 10:00'\nset @date2='2008-1-1 22:00'\nif @date1=@date2\n    print 'Equal'\nelse\n    print 'Not equal'\n</code></pre>\n\n<p>For SQL 2005 and earlier what you can do is convert the date to a varchar in a format that does not have the time component. For instance the following returns 2008.08.22</p>\n\n<pre><code>select convert(varchar,'2008-08-22 18:11:14.133',102)\n</code></pre>\n\n<p>The 102 part specifies the formatting (Books online can list for you all the available formats)</p>\n\n<p>So, what you can do is write a function that takes a datetime and extracts the date element and discards the time. Like so:</p>\n\n<pre><code>create function MakeDate (@InputDate datetime) returns datetime as\nbegin\n    return cast(convert(varchar,@InputDate,102) as datetime);\nend\n</code></pre>\n\n<p>You can then use the function for companions</p>\n\n<pre><code>Select * from Orders where dbo.MakeDate(OrderDate) = dbo.MakeDate(DeliveryDate)\n</code></pre>\n"}, {'answer_id': 22662, 'author': 'brendan', 'author_id': 225, 'author_profile': 'https://Stackoverflow.com/users/225', 'pm_score': 0, 'selected': False, 'text': '<p>I would use the dayofyear function of datepart:</p>\n\n<pre><code>\nSelect *\nfrom mytable\nwhere datepart(dy,date1) = datepart(dy,date2)\nand\nyear(date1) = year(date2) --assuming you want the same year too\n</code></pre>\n\n<p>See the datepart reference <a href="http://www.tizag.com/sqlTutorial/sqldatepart.php" rel="nofollow noreferrer">here</a>.</p>\n'}, {'answer_id': 22670, 'author': 'Tundey', 'author_id': 1453, 'author_profile': 'https://Stackoverflow.com/users/1453', 'pm_score': 0, 'selected': False, 'text': '<p>Regarding timezones, yet one more reason to store all dates in a single timezone (preferably UTC). Anyway, I think the answers using datediff, datepart and the different built-in date functions are your best bet.</p>\n'}, {'answer_id': 22802, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 2, 'selected': False, 'text': "<p>Eric Z Beard:</p>\n\n<blockquote>\n  <p>I do store all dates in GMT. Here's the use case: something happened at 11:00 PM EST on the 1st, which is the 2nd GMT. I want to see activity for the 1st, and I am in EST so I will want to see the 11PM activity. If I just compared raw GMT datetimes, I would miss things. Each row in the report can represent an activity from a different time zone.</p>\n</blockquote>\n\n<p>Right, but when you say you're interested in activity for Jan 1st 2008 EST:</p>\n\n<pre><code>SELECT @activityDateMidnight = '1/1/2008', @activityDateTZ = 'EST'\n</code></pre>\n\n<p>you just need to convert <em>that</em> to GMT (I'm ignoring the complication of querying for the day before EST goes to EDT, or vice versa):</p>\n\n<pre><code>Table: TimeZone\nFields: TimeZone, Offset\nValues: EST, -4\n\n--Multiply by -1, since we're converting EST to GMT.\n--Offsets are to go from GMT to EST.\nSELECT @activityGmtBegin = DATEADD(hh, Offset * -1, @activityDateMidnight)\nFROM TimeZone\nWHERE TimeZone = @activityDateTZ\n</code></pre>\n\n<p>which should give you '1/1/2008 4:00 AM'. Then, you can just search in GMT:</p>\n\n<pre><code>SELECT * FROM EventTable\nWHERE \n   EventTime &gt;= @activityGmtBegin --1/1/2008 4:00 AM\n   AND EventTime &lt; (@activityGmtBegin + 1) --1/2/2008 4:00 AM\n</code></pre>\n\n<p>The event in question is stored with a GMT EventTime of 1/2/2008 3:00 AM. You don't even need the TimeZone in the EventTable (for this purpose, at least). </p>\n\n<p>Since EventTime is not in a function, this is a straight index scan - which should be pretty efficient. Make EventTime your clustered index, and it'll fly. ;)</p>\n\n<p>Personally, I'd have the app convert the search time into GMT before running the query.</p>\n"}, {'answer_id': 23856, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 1, 'selected': False, 'text': "<p>Eric Z Beard:</p>\n\n<blockquote>\n  <p>the activity date is meant to indicate the local time zone, but not a specific one</p>\n</blockquote>\n\n<p>Okay - back to the drawing board. Try this:</p>\n\n<pre><code>where t.TheDateINeedToCheck BETWEEN (\n    dateadd(hh, (tz.Offset + ISNULL(ds.LocalTimeZone, 0)) * -1, @ActivityDate)\n    AND\n    dateadd(hh, (tz.Offset + ISNULL(ds.LocalTimeZone, 0)) * -1, (@ActivityDate + 1))\n)\n</code></pre>\n\n<p>which will translate the @ActivityDate to local time, and compare against that. That's your best chance for using an index, though I'm not sure it'll work - you should try it and check the query plan.</p>\n\n<p>The next option would be an indexed view, with an indexed, computed TimeINeedToCheck <em>in local time</em>. Then you just go back to:</p>\n\n<pre><code>where v.TheLocalDateINeedToCheck BETWEEN @ActivityDate AND (@ActivityDate + 1)\n</code></pre>\n\n<p>which would definitely use the index - though you have a slight overhead on INSERT and UPDATE then.</p>\n"}]
['https://Stackoverflow.com/questions/22570', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1219/']