Date comparison better method

Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and 
'2010-06-17 23:59:59.997'

Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' AND 
DateColumn < '2010-06-17 23:59:59.997'

I am seeing in a project both the above methods of data range filering is 
happening in different SPs. I am trying to understand which is the better 
method of comparing two date values and why? [Btw i know BETWEEN considers 
both the upper and lower limit]

Regards
Pradeep
0
Utf
6/18/2010 12:51:35 AM
sqlserver.programming 1873 articles. 0 followers. Follow

7 Replies
422 Views

Similar Articles

[PageSpeed] 37

I would say the following is the better approach:

SELECT ..
FROM table
WHERE datecolumn >= '20100601 00:00:00.000'
AND datecolumn < '20100618 00:00:00.000'

Using midnight of the day after the end date range will guarantee that you 
include all dates up to that time, but not including that time.  The problem 
with using between and calculating the last date with .997 is that the new 
datetime2 datatype in SQL Server 2008 has higher precision, which will be 
lost if the code isn't updated to account for it.

Jeff

"SqlBeginner" <SqlBeginner@discussions.microsoft.com> wrote in message 
news:8DA7490F-60A5-48BF-BD7F-5D1E1E6D590B@microsoft.com...
> Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and
> '2010-06-17 23:59:59.997'
>
> Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' 
> AND
> DateColumn < '2010-06-17 23:59:59.997'
>
> I am seeing in a project both the above methods of data range filering is
> happening in different SPs. I am trying to understand which is the better
> method of comparing two date values and why? [Btw i know BETWEEN considers
> both the upper and lower limit]
>
> Regards
> Pradeep 

0
Jeffrey
6/18/2010 2:28:28 AM
Thanks for the response Jeff. I don't see the system getting migrated to SQL 
2008 in near future as the shop i am working on has finished migrating to 
2005 :) So I would greatly appreciate if you could let me know for this 
context which one be better than the other and why is it so? 

Regards
Pradeep

"Jeffrey Williams" wrote:

> I would say the following is the better approach:
> 
> SELECT ..
> FROM table
> WHERE datecolumn >= '20100601 00:00:00.000'
> AND datecolumn < '20100618 00:00:00.000'
> 
> Using midnight of the day after the end date range will guarantee that you 
> include all dates up to that time, but not including that time.  The problem 
> with using between and calculating the last date with .997 is that the new 
> datetime2 datatype in SQL Server 2008 has higher precision, which will be 
> lost if the code isn't updated to account for it.
> 
> Jeff
> 
> "SqlBeginner" <SqlBeginner@discussions.microsoft.com> wrote in message 
> news:8DA7490F-60A5-48BF-BD7F-5D1E1E6D590B@microsoft.com...
> > Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and
> > '2010-06-17 23:59:59.997'
> >
> > Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' 
> > AND
> > DateColumn < '2010-06-17 23:59:59.997'
> >
> > I am seeing in a project both the above methods of data range filering is
> > happening in different SPs. I am trying to understand which is the better
> > method of comparing two date values and why? [Btw i know BETWEEN considers
> > both the upper and lower limit]
> >
> > Regards
> > Pradeep 
> 
0
Utf
6/18/2010 7:42:09 AM
Does SQL Server internally resolves the BETWEEN operator to >= and < ? If so 
won't it be better to use >= and < directly and save some time?

Regards
Pradeep

"SqlBeginner" wrote:

> Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and 
> '2010-06-17 23:59:59.997'
> 
> Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' AND 
> DateColumn < '2010-06-17 23:59:59.997'
> 
> I am seeing in a project both the above methods of data range filering is 
> happening in different SPs. I am trying to understand which is the better 
> method of comparing two date values and why? [Btw i know BETWEEN considers 
> both the upper and lower limit]
> 
> Regards
> Pradeep
0
Utf
6/18/2010 8:27:04 AM
> Does SQL Server internally resolves the BETWEEN operator to >= and < ? If 
> so
> won't it be better to use >= and < directly and save some time?

No, the BETWEEN operator is equivalent to >= and <=.  You can see this when 
you view the predicate in the query plan.

I agree with Jeffrey that it is best to specify an inclusive start value and 
and exclusive end value (>= and <) when working with datetime data.  This 
way, you will get the expected behavior regardless of the underlying 
date/time data type and whether or not the underlying data includes a time 
component.  The milliseconds specification with BETWEEN or <= is at best a 
kludge, albeit I have used it myself before I learned better :-)

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"SqlBeginner" <SqlBeginner@discussions.microsoft.com> wrote in message 
news:E6874214-5462-4378-B976-C9CFC73F156F@microsoft.com...
> Does SQL Server internally resolves the BETWEEN operator to >= and < ? If 
> so
> won't it be better to use >= and < directly and save some time?
>
> Regards
> Pradeep
>
> "SqlBeginner" wrote:
>
>> Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' 
>> and
>> '2010-06-17 23:59:59.997'
>>
>> Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' 
>> AND
>> DateColumn < '2010-06-17 23:59:59.997'
>>
>> I am seeing in a project both the above methods of data range filering is
>> happening in different SPs. I am trying to understand which is the better
>> method of comparing two date values and why? [Btw i know BETWEEN 
>> considers
>> both the upper and lower limit]
>>
>> Regards
>> Pradeep 

0
Dan
6/18/2010 12:11:26 PM
SET SHOWPLAN_ALL ON
GO

I wrote one query with BETWEEN and one query with >= and < .. in the query 
plan i see that BETWEEN is also being replaced as >= and <

See the extract of that output

....... BETWEEN '20100601' AND '20100601'
  |--Table Scan(OBJECT:([DBName].[dbo].[TblName]), 
WHERE:([DBName].[dbo].[TblName].[dtCreateDate]>=CONVERT_IMPLICIT(datetime,[@1],0) 
AND 
[DBName].[dbo].[TblName].[dtCreateDate]<=CONVERT_IMPLICIT(datetime,[@2],0)))

....... >= '2010-06-01' AND    dtCreateDate < '2010-06-17 23:59:59.997'
  |--Table Scan(OBJECT:([DBName].[dbo].[TblName]), 
WHERE:([DBName].[dbo].[TblName].[dtCreateDate]>=CONVERT_IMPLICIT(datetime,[@1],0) 
AND 
[DBName].[dbo].[TblName].[dtCreateDate]<CONVERT_IMPLICIT(datetime,[@2],0)))

Regards
Pradeep

"Dan Guzman" wrote:

> > Does SQL Server internally resolves the BETWEEN operator to >= and < ? If 
> > so
> > won't it be better to use >= and < directly and save some time?
> 
> No, the BETWEEN operator is equivalent to >= and <=.  You can see this when 
> you view the predicate in the query plan.
> 
> I agree with Jeffrey that it is best to specify an inclusive start value and 
> and exclusive end value (>= and <) when working with datetime data.  This 
> way, you will get the expected behavior regardless of the underlying 
> date/time data type and whether or not the underlying data includes a time 
> component.  The milliseconds specification with BETWEEN or <= is at best a 
> kludge, albeit I have used it myself before I learned better :-)
> 
> -- 
> Hope this helps.
> 
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> 
> "SqlBeginner" <SqlBeginner@discussions.microsoft.com> wrote in message 
> news:E6874214-5462-4378-B976-C9CFC73F156F@microsoft.com...
> > Does SQL Server internally resolves the BETWEEN operator to >= and < ? If 
> > so
> > won't it be better to use >= and < directly and save some time?
> >
> > Regards
> > Pradeep
> >
> > "SqlBeginner" wrote:
> >
> >> Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' 
> >> and
> >> '2010-06-17 23:59:59.997'
> >>
> >> Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' 
> >> AND
> >> DateColumn < '2010-06-17 23:59:59.997'
> >>
> >> I am seeing in a project both the above methods of data range filering is
> >> happening in different SPs. I am trying to understand which is the better
> >> method of comparing two date values and why? [Btw i know BETWEEN 
> >> considers
> >> both the upper and lower limit]
> >>
> >> Regards
> >> Pradeep 
> 
0
Utf
6/18/2010 2:12:41 PM
The >= and < method is the most sound and will make it so that you
don't ever have to worry about how the dates are populated or the
datatypes.  The only time to use BETWEEN (or >= and <=) is when you
are including the date and time in your query and you know the actual
time that is being used (your not estimating it.)  Using these
comparisons also guarantees that any indexes on the date columns in
2005 will be used (as opposed to comparing the results of a
Month(datevalue) or a DATEDIFF() on the date value to some other
value.)

-Eric Isaacs
0
Eric
6/18/2010 5:39:01 PM
If you have SQL Server 2008, then use the DATE data type and the
BETWEEN predicate. It is easier to read and eventually SQL Server will
optimze for it like other SQLs that have had it for decades.

If you have to use the DATETIME data type, then either of the two ways
will work, but there is a subtle difference. BETWEEN includes the end
points:

 date_col BETWEEN '2010-06-01' AND '2010-06-17 23:59:59.997'

means

((date_col >= '2010-06-01') AND (date_col <= '2010-06-17
23:59:59.997'))

If you know that the precision of date_col will not get ot decimal
seconds, then it does not matter. But the BETWEEN is easier to read
and to edit when you move to SS-2008.

0
CELKO
6/19/2010 1:15:44 PM
Reply:

Similar Artilces:

Date compatibility error
Hi All, I was hoping I could get some help with my problem, as i have trie everything i know. I have this spreadsheet (attached) and when i type the date in m formula doesnt want to find the corresponding data. But when the date are copied from the data table and pasted in (same dates) it finds th data fine. I have tried formatting the dates in the same way but with no change. NB: If possible i would like to keep the formatting on the data tabl without changing as this is a direct data import from another program If this is the only possibility, let me know and I will try and fix i in a ...

changing date format
hi, I'm trying over and over to change the date format cells,and it just doesnt allowed me,allow is not the right word - it just does nothing,or it changes only a few of the cells to the wanted format. I tried to change other date format cells on other file it went just fine,so why is it happening on the first file i was talking about ? what should i do ? thank you in advance,hummiz -- hummiz ------------------------------------------------------------------------ hummiz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23877 View this thread: http://www...

Formatting dates by year
I would like to know if there is a way to format dates so that they are formatted by year only. For example, if I click on Format -> Cells, there are a bunch of options, but none of them list to show the year only, just mm/dd/yy or mm/yy, etc. I am trying to create a chart so that only the year shows up. Thanks. HI try clicking format cells custom then in the 'type' box, enter yyy -- Message posted from http://www.ExcelForum.com Hi, I have tried this, but what it does is give one specific year (ie. 1905) for all the years I have entered. I want to just keep my values. ...

Formatting text and date together
How can I format a cell to hold text ("Last update:") and a date whic is obtained from another cell? The Formula reads something like +"Las update: "&f2. The date comes out as its numerical value. Is it possible. Thank -- Brisbane Ro ----------------------------------------------------------------------- Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2509 View this thread: http://www.excelforum.com/showthread.php?threadid=49227 Use the text function to format the numeric component to suit your needs, along the lines of ...

Date and Time Table Modified
I have 4 forms used by managers to enter data. When they enter data, the data is saved in tables. I would like to include a date and time stamp so that I know when they entered each piece of new data. Do I need to have a field in my table to collect the date and time? How do I collect that in my form? Thanks!! Yes, you need to add a field to your table. In the form's BeforeUpdate event, put code to update that field. Assuming that you named the field LastUpdated and that you included the field in the form's RecordSource, the code would look something like: Private Sub Form_Bef...

Next Date given one date
At this time, I cannot use VBA. Here is my objective: For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates are: 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be...

Date and Number cell formats
I am using Excel 2003 and I am trying to create a very basic weight chart by date. Column one is the date and column two is the weight. I would like all of the dates to appears as 01/06/2010, 10/05/2010, 11/15/2010, etc. -- in other words, mm/dd/yyyy with 2 digits for the month, 2 digits for the day, and 4 digits for the year. Instead, all I get for the date is: 1 or 2 digits for the month, depending on whether the month is a 2-digit or 1-digit number; and the same for the day, 1 or 2 digits. I want them all to appear as mm/dd/yyyy. I would like the weights to appear as 211.6 , 212.0...

Business Portal Project Timesheet date header dissappear when scroll down
In Business Portal Project timesheet, when scroll down the cost category, the date header is dissappear from the page, is this a design fault ? For project will large no of cost category, it is very in-convenient as we did not know whether the hours being keyed into the correct date. Please advise. Thanks ...

AUTO DATE RECOGNITION
HOW CAN I STOP PART NUMBERS WHICH HAVE DASHES IN THEM BEING CONVERTED INTO DATES WHEN I DO A DATA IMPORT INTO EXCEL? Yell real LOUD. That does it for me. Seriously, please don't use all caps for subject or body of msg. It is considered shouting and rude. Try pre-formatting your destination range to text. -- Don Guillett SalesAid Software donaldb@281.com "TONY" <anonymous@discussions.microsoft.com> wrote in message news:AD32B08C-CD73-4585-B706-6F29B17894D0@microsoft.com... > HOW CAN I STOP PART NUMBERS WHICH HAVE DASHES IN THEM BEING CONVERTED INTO DATES WHEN I DO A ...

Outlook Archive NOT using Modified date
Is there anyway to force Archive to look at sent or received and not consider the modified date to archive items. We put in a new server and migrated all the mail to the new server so now the modified date on all emails has changed to that date. Now everything looks only a couple of months old even email from 2 and 3 years ago. I need to get archive to ignore the modified date. Any way to do this? No. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engine...

European vs american date notations
Hi, I have a strange problem in Excel. I want to enter a european date 20/09/2004 and convert it into dd/mmm/yyyy what should display 20 Sep 2004 BUT it does not it stays like 20/09/2004. WHen I rewrite the date in 09/20/2004 and convert it does show 20 Sep 2004. The cell properties is set to english date and internation setting is also english. Why cant I enter european dates into excel.. Excel uses your Control Panel date settings to help determine what kind of an entry you are making. While 20/09/2004 is a valid date under many other countries' settings it is not with U.S. s...

Workflow Call Assembly to add Date or Time
I cannot get a very simple workflow to work. All I want it to do is update a datetime field to add one month! Workflow monitor says it completed, but it has never worked... Has anyone ever been able to make this work? On 26 Mar, 18:54, "DubSport" <jamie.carmich...@cmgl.ca> wrote: > I cannot get a very simple workflow to work. All I want it to do is > update a datetime field to add one month! Workflow monitor says it > completed, but it has never worked... Has anyone ever been able to > make this work? Show your workflow rule OK here it is. its a manual rule, s...

Voucher payables document date 2018
A voucher (check) was posted with the Document Date of 12/17/2018. The invoice itself was dated 12/17/2008. We are trying to VOID this transaction but I get: "The void date cannot be before the document date" (meaning today of 1/16/2009 is before 12/17/2008) The Payables Transaction Posting Journal/Payables Management shows INV Voucher # with the doc date as 12/17/2008 but Check reconciliation cannot be completed because this check is out of the period. Please advise best method of fixing this error. Thank you Pam, Either go into the table and change the document date or c...

I fixed the date and time and the time zone. Now reply me please
Hi, I have some questions in outlook 2003, can anyone help me? 1-how can i in the appointment type a subject with special format (red, bold..) 2-whent a task is 100% completed, how can be deleted automatically. 3- I need to filter only the available time on a specific day. ex if i have 2 appointments today on a specific time, what i need to see is the available time for other new appointments. 4-If I have a lot of appointments on a specific date that are colored with labels and have a specific show time as (busy,tentative), when i go to print this calendar with the "calendar detai...

Date and Time ??
I had a question working about date and time it had 11 or 12 posts and now it's gone. Any Idea what happened to it? Sorry I found it. "Duane" wrote: > I had a question working about date and time it had 11 or 12 posts and now > it's gone. > > Any Idea what happened to it? "Duane" <Duane@discussions.microsoft.com> wrote in message news:9F0A6769-90FA-4E91-81AF-22F2B7549C32@microsoft.com... >I had a question working about date and time it had 11 or 12 posts and >now > it's gone. > > Any Idea what happen...

default date in Excel
How do you change the default date format in Excel. I am running Excel 2000 but we also have Excel 2002 and Excel 2003 in our office. Will Fleenor will@k2e.com Hi Will It's a Windows setting. Change it in the control panel. HTH. Best wishes Harald "Will Fleenor" <will@k2e.com> skrev i melding news:Of7bGQJXFHA.1148@tk2msftngp13.phx.gbl... > How do you change the default date format in Excel. > > I am running Excel 2000 but we also have Excel 2002 and Excel 2003 in our > office. > > Will Fleenor > will@k2e.com > > ...

execute method doesnt work
I have two Custom webpages both of them uses Current user information to get details about their opportunity. Both of them using Following code. One of the page works fine but otherone does nt. I dont know whats wrong with the second page. The code that it is using is below. service = new CrmService(); service.Url = "http://<server>:5555/mscrmservices/2006/crmservice.asmx" service.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials; WhoAmIRequest userRequest = new WhoAmIRequest(); WhoAmIResponse user = (WhoAmIResponse)service.Execute(userRequest); on...

formatting a date in a text field
I need to format a date that is in a text field - ex. 20081108 needs to be 11/08/2008 - how do I do this? p.s. I cannot change the field from a text to a date - it deletes all the info First, to change the text field to a date format: Format(DateSerial(Left([DateField],4),Mid([DateField],5,2),Right([DateField],2),"mm/dd/yyyy") It would probably be best to change the field to date type for programming and reporting down the line. To do this (first backup your data) then add a new field to your table (with a type of date). Do an update query to populate the new field with an ...

How do I display the most recent of three dates in an Access quer.
How do I display the most recent (or latter) of three dates in an Access query? I'm assuming that the date field is truely a date/time datatype and not a text field with something that looks like a date. 1. Create a query and sort on that field in descending order. Run it to make sure it looks correct. 2. Open the query in SQL view. Change where it says SELECT to SELECT TOP 3 One caution: If there is a tie for 3rd place, Access will show all the tied records. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. ...

Why does this not work (selecting a date) ?
SELECT CDate((Month(RecordDate) & '/' & Day(RecordDate) & '/' & Year(RecordDate)) AS RecDate This converts 8/31/2007 to 8/1/2007 Works fine for me with 2003 SP2. You omitted a closing parenthesis. -- KARL DEWEY Build a little - Test a little "mscertified" wrote: > SELECT CDate((Month(RecordDate) & '/' & Day(RecordDate) & '/' & > Year(RecordDate)) AS RecDate > > This converts 8/31/2007 to 8/1/2007 Where is the missing parentheses? I count 4 opening and 4 closing??? "KARL DEWEY" wrote: >...

Julian to Gregorian date conversion
how do I convert jualian date to gregorian and back in access 97, preferably by not using VB Code if possible. What is the format of your Julian date and why the aversion to code? Tommo wrote: >how do I convert jualian date to gregorian and back in access 97, preferably >by not using VB Code if possible. -- HTH - RuralGuy (RG for short) acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200707/1 On Wed, 25 Jul 2007 09:32:07 -0700, Tommo <Tommo@discussions.microsoft....

Tenure Date Formula
I am trying to figure out a formula using today date - start date= years.months in service. Can someone help me. Thanks. -- adams77 ------------------------------------------------------------------------ adams77's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23695 View this thread: http://www.excelforum.com/showthread.php?threadid=567485 http://www.cpearson.com/excel/datedif.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "adams77" <adams77.2bx4r3_1154531708.141@excelforum-nospam.com> wrote in message news:adams77.2bx4r3_115453...

Is there a simlpe SOAP -> method call example that doesn't use any transports, etc.?
I have not had any luck searching for a simple, simple example that calls a method on an object given a SOAP string. Here's what I'm looking for (in pseudocode): string SOAPCall = "bla bla bla"; // SOAP method invocation XML string SOAPResponse; MyObject x = new MyObject(); // and here's where I don't know what to do /* call the method on x as specified in SOAPCall and get the output into SOAPResponse. */ Can anyone be of any assistance? Thank you! "Nimai Malle" <nimai_malle@yahoo.com> wrote in message news:7ace219f.0408200729.29ba1498@posting....

Conditional formatting dates
Hi, Help please. I have a column of 'Due Dates' in the mmm-yy format. I want the highlight a 'date' a month before it is due to expire. How is it done? I can get so far with DATEVALUE but then become stucK. Many thanks Hi Lofty, For a date in A1, you could use a formula for the conditional format like: =A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) -- Cheers macropod [Microsoft MVP - Word] "Lofty" <Lofty@discussions.microsoft.com> wrote in message news:848E191C-832F-4C60-924D-32A1AF70A0FA@microsoft.com... > Hi, > Help ...

DBTYP.NET Studio 2010
DBTYP.NET Studio 2010 - Database Comparison Suite Released Vienna, Austria - March 8, 2010 Today, BYPsoft announced the availability of DBTYP.NET Studio 2010, the newest version of the cross-database comparison tool that compares SQL Server, MySQL and Oracle databases (schema and data). DBTYP.NET Studio 2010 is available for download from http://www.bypsoft.com. With its rich support for schema and data cross-database comparison, DBTYP.NET Studio takes the mystery out of databases, making their comparison practical and easy for programmers and database administrators everywh...