Problem with using BETWEEN for date range...

[Jim Armstrong]

Hi all -

This problem has been driving me crazy, and I'm hoping the answer is
something stupid I am neglecting to see....

The procedure posted below is part of an Access/SQL database I have
developed. Basically, the user would input a beginning and ending date,
and the query goes and pulls records that meet the following criteria:

1. TradeDate is between beginning date and ending date
2. TradeTime is between beginning date and ending date
3. Trade's Match ID is equal to match IDs returned by the second select
statement - this is part of a ticket processing system, and tickets are
grouped using their match id. So, if one ticket has been updated and
now meets criteria #1 or #2 above, this is supposed to also return any
of the other tickets with the same match ID - so if one ticket in a
group changes, our acct. dept can look at the whole group on their
reports.

Anyway, the query below seems to work, but I am not happy with it. The
problem was I was using the BETWEEN function, and not converting all
the dates to varchar. This worked fine, unless the beginning date and
ending date were the same. For example, if I had a ticket with a
tradedate of 5/3/06 and I ran a beginning date of 5/3/06 and an ending
date of 5/3/06, the ticket should be returned. However, with the
BETWEEN statement, it would return no rows.

I changed the BETWEEN statements to statements like:

tradedate >= beginning date and tradedate <= ending date

but this also returned no rows.

It was only upon converting all the dates to varchar and using the <=
and >= operators that I started getting the results I need.

Can someone tell me why the heck BETWEEN wouldn't work? Tradedate and
Tradetime are both datetime values, and I was bringing in the beginning
and ending date variables in datetime form...is there a problem using
BETWEEN when the first and second variables used are the same?

Any light you can shed on this would be great, because having all these
convert statements and such makes me nervous...I'd rather get between
to work, but I have not been able to in my testing...

Thanks! -Jim

CREATE PROCEDURE dbo.spAcctExpor t(@begindate datetime,
@enddate datetime)
AS SELECT TOP 100 PERCENT dbo.tblTradeAcc ount.Accounting Code AS
TradeAccount, dbo.tblOrders.T icketNum, dbo.tblOrders.T radeDate,
dbo.tblOrders.S ettleDate, NULL AS
ProductionMonth , dbo.tblOrders.R epID, dbo.tblOrders.A cctNum,
dbo.tblAccounts .Shortname, dbo.tblOrders.Q uantity,
dbo.TBLCUSIP.Fa ctor, dbo.tblOrders.B uySell,
dbo.tblOrders.C USIP, dbo.TBLCUSIP.Is suer, dbo.TBLCUSIP.Po olNum,
dbo.TBLCUSIP.Co upon,
dbo.tblOrders.F ixAdj, dbo.tblOrders.P rice,
dbo.tblOrders.R epSC, '=(H:H*I:I*Q:Q)/100' AS Markup, 'PTMSA' AS
ProdType, dbo.tblOrders.D eskSC,
dbo.tblOrders.R epCarry, '=(H:H*I:I*T:T)/100' AS
DeskMarkup, dbo.tblOrders.M atchID,

'=IF(K:K="B",(( I:I*H:H*P:P)/100)*-1,(I:I*H:H*P:P)/100)' AS
TotalPrincipal, dbo.tblOrders.C ancelCorrect,
dbo.tblOrders.O riginalTrade,
dbo.tblOrders.T radeTime, dbo.tblOrders.R ep2ID,
dbo.tblOrders.R ep2SC
FROM dbo.tblOrders INNER JOIN
dbo.TBLCUSIP ON dbo.tblOrders.C USIP =
dbo.TBLCUSIP.CU SIP INNER JOIN
dbo.tblAccounts ON dbo.tblOrders.A cctNum =
dbo.tblAccounts .AcctNum INNER JOIN
dbo.tblTradeAcc ount ON dbo.tblOrders.T radeAccount
= dbo.tblTradeAcc ount.TradeAccou nt
WHERE ((

(dbo.tblOrders. MatchID IS NOT NULL) AND (dbo.tblOrders. MatchID IN
(SELECT MatchID FROM dbo.tblOrders WHERE (

CONVERT(VARCHAR (10),dbo.tblOrd ers.TradeDate,1 01) >=
CONVERT(VARCHAR (10), @begindate,101) AND
CONVERT(VARCHAR (10),DBO.TBLORD ERS.TRADEDATE,1 01) <=
CONVERT(VARCHAR (10), @enddate,101))

OR (

CONVERT(VARCHAR (10),dbo.tblOrd ers.TradeTime,1 01) >=
CONVERT(VARCHAR (10), @begindate,101) AND
CONVERT(VARCHAR (10),DBO.TBLORD ERS.TRADETIME,1 01) <=
CONVERT(VARCHAR (10), @enddate,101))) ))

OR ((

CONVERT(VARCHAR (10),dbo.tblOrd ers.TradeDate,1 01) >=
CONVERT(VARCHAR (10), @begindate,101) and
CONVERT(VARCHAR (10),DBO.TBLORD ERS.TRADEDATE,1 01) <=
CONVERT(VARCHAR (10), @enddate,101))

OR (

CONVERT(VARCHAR (10),DBO.TBLORD ERS.TradeTime,1 01) >=
CONVERT(VARCHAR (10), @begindate,101) AND
CONVERT(VARCHAR (10),DBO.TBLORD ERS.TRADETIME,1 01) <=
CONVERT(VARCHAR (10), @enddate,101))) )

ORDER BY dbo.tblOrders.C ancelCorrect,
dbo.tblTradeAcc ount.Accounting Code, dbo.tblOrders.M atchID,
dbo.tblOrders.B uySell, dbo.tblOrders.T icketNum
GO

[--CELKO--]

Re: Problem with using BETWEEN for date range...

In SQL Server, there is not DATE data type or TIME data type -- but
there is a single DATETIME. Quit doing all that converting and work
with temporal expressions instead of COBOL-style strings. Think in
abstract terms, not how it is displayed on a piece of paper or a
screen.

WHERE trade_time BETWEEN start_time AND end_time

will work fine, if you bother to add constraints to start_time (i.e
00:00:00) and end_time (23:59:59.9999) columns. Another trick is to
allow end_time to be NULL when an event is still in progress and then
use COALESCE (end_time, CURRENT_TIMESTA MP)

If you look up Rick Snodgrass at University of Arizona, you can
download his out-of-print book on temporal SQL queries.

[Jim Armstrong]

Re: Problem with using BETWEEN for date range...

OK - my post mentioned I originally used

WHERE tradedate BETWEEN begindate AND enddate

Begindate and enddate are not columns - they are parameters the user
supplies in the format mm/dd/yyyy.

My whole post was explaining that I would *prefer* to use the BETWEEN
AND statement - the converting was not my choice, but it is the only
way this query works when the user specifices the same date as
begindate and enddate.

So once again - can someone explain why this query returns no records
when begindate and enddate are specified as 5/3/2006 and there is
clearly trade data with that tradetime?

[Roy Harvey]

Re: Problem with using BETWEEN for date range...

My assumption is that the tradedate is a datetime datatype that
includes a time, while the begin and end dates are just dates. The
comparison is will be done on datetimes, which include both date and
time.
[color=blue]
>WHERE tradedate BETWEEN begindate AND enddate[/color]

Consider what this can look like when we substitute actual values.

WHERE
'2006-05-03 17:12:51.420' BETWEEN
'2006-05-03 00:00:00.000' AND
'2006-05-03 00:00:00.000'

The time that is part of tradedate is putting it outside of the
"range" of the BETWEEN; in this case of course the BETWEEN has no
range, being two identical points in time. To match it needs a 24
hour range, but that is not what it has.

To use BETWEEN you would need toto reduce tradedate to a simple date
with no time for the test.

Roy Harvey
Beacon Falls, CT

On 3 May 2006 13:40:16 -0700, "Jim Armstrong"
<armstrongjc@ho tmail.com> wrote:
[color=blue]
>OK - my post mentioned I originally used
>
>WHERE tradedate BETWEEN begindate AND enddate
>
>Begindate and enddate are not columns - they are parameters the user
>supplies in the format mm/dd/yyyy.
>
>My whole post was explaining that I would *prefer* to use the BETWEEN
>AND statement - the converting was not my choice, but it is the only
>way this query works when the user specifices the same date as
>begindate and enddate.
>
>So once again - can someone explain why this query returns no records
>when begindate and enddate are specified as 5/3/2006 and there is
>clearly trade data with that tradetime?[/color]

[Erland Sommarskog]

Re: Problem with using BETWEEN for date range...

Jim Armstrong (armstrongjc@ho tmail.com) writes:[color=blue]
> CONVERT(VARCHAR (10),dbo.tblOrd ers.TradeDate,1 01) >=
> CONVERT(VARCHAR (10), @begindate,101) AND
> CONVERT(VARCHAR (10),DBO.TBLORD ERS.TRADEDATE,1 01) <=
> CONVERT(VARCHAR (10), @enddate,101))[/color]

But this does not make any sense. When I run

select convert(varchar (10), getdate(), 101)

I get

05/03/2006

So if the user enters 2006-05-03, you will also give him hits from
2005???

If you feel compelled to use string format, use format 112 which is
YYYYMMDD, which is possible to compare, and which also is unambiguosly
convertible back to date.

But it would make more sense to simple say:

O.TradeDate BETWEEN @begindate AND @enddate

Provided that is, that TradeDate always have a timeportion of midnight.
And the same provision applies to @begindate and @endate, but I got
the impression that the user enters date only.

If TradeDate also includes hours and seconds, you need to write:

O.TradeDate >= @begindate AND
O.TradeDate < dateadd(DAY, 1, @enddate)

There is one more important thing to observer here: you should avoid
putting TradeDate in an expression. This is because if there is an
index on TradeDate, that index cannot be used if you put TradeDate in
an expression, and performance will suffer.
[color=blue]
> CONVERT(VARCHAR (10),dbo.tblOrd ers.TradeTime,1 01) >=
> CONVERT(VARCHAR (10), @begindate,101) AND
> CONVERT(VARCHAR (10),DBO.TBLORD ERS.TRADETIME,1 01) <=
> CONVERT(VARCHAR (10), @enddate,101))) ))[/color]

The same applies here, although I'm not really clear what is in TradeTime.
Is TradeDate 2006-05-03 00:00:00 and TradeTime is 2006-05-03 12:23:23?

In such case, I'm not sure why you need to check both.
[color=blue]
> CONVERT(VARCHAR (10),dbo.tblOrd ers.TradeDate,1 01) >=
> CONVERT(VARCHAR (10), @begindate,101) and
> CONVERT(VARCHAR (10),DBO.TBLORD ERS.TRADEDATE,1 01) <=
> CONVERT(VARCHAR (10), @enddate,101))
>
> OR (
>
> CONVERT(VARCHAR (10),DBO.TBLORD ERS.TradeTime,1 01) >=
> CONVERT(VARCHAR (10), @begindate,101) AND
> CONVERT(VARCHAR (10),DBO.TBLORD ERS.TRADETIME,1 01) <=
> CONVERT(VARCHAR (10), @enddate,101))) )[/color]

And then you have it once more?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Re: Problem with using BETWEEN for date range...

>> So once again - can someone explain why this query returns no records [sic] when begindate and enddate are specified as 5/3/2006 and there is clearly trade data with that tradetime? <<

Once again, because there is a DATETIME column which has a time on it.
If you do not give the time, it defautls to 00:00:00.000 Hrs. This is
a single point in time. What you wanted to use was

WHERE tradedate BETWEEN '2006-05-03 00:00:00.000' AND '2006-05-03
23:59:59.99'

You might also want to learn ISO standards and use 'yyyy--mm-dd' for
date values.

And do not forget to add constraints to temporal columns in your base
tables.

[Tony Rogerson]

Re: Problem with using BETWEEN for date range...

Yes but hang on a minute, what if you don't want a time, holding the time
component is misleading that indicates the data value may contain a time
between 00:00:00 and 23:59:59, being 00:00:00 means the trade was done at
midnight which may well not be true, consider settment date where the trade
needs to settle on a particular day, not by midnight but sometime on that
day.

In SQL Server because we don't have a DATE data type we need to store dates
as an integer in the form yyyymmdd which is unfortunate but the reality.
[color=blue]
> You might also want to learn ISO standards and use 'yyyy--mm-dd' for
> date values.[/color]

Rich coming from the guy who doesn't use the standard format himself.

2006-05-03 00:00:00.000 should be written 2006-05-03T00:00:00.000

OR

20060503 if you are only specifying the date.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@eart hlink.net> wrote in message
news:1146749926 .099160.51660@i 40g2000cwc.goog legroups.com...[color=blue][color=green][color=darkred]
>>> So once again - can someone explain why this query returns no records
>>> [sic] when begindate and enddate are specified as 5/3/2006 and there is
>>> clearly trade data with that tradetime? <<[/color][/color]
>
> Once again, because there is a DATETIME column which has a time on it.
> If you do not give the time, it defautls to 00:00:00.000 Hrs. This is
> a single point in time. What you wanted to use was
>
> WHERE tradedate BETWEEN '2006-05-03 00:00:00.000' AND '2006-05-03
> 23:59:59.99'
>
> You might also want to learn ISO standards and use 'yyyy--mm-dd' for
> date values.
>
> And do not forget to add constraints to temporal columns in your base
> tables.
>[/color]

[Erland Sommarskog]

Re: Problem with using BETWEEN for date range...

Tony Rogerson (tonyrogerson@s qlserverfaq.com ) writes:[color=blue]
> Yes but hang on a minute, what if you don't want a time, holding the
> time component is misleading that indicates the data value may contain a
> time between 00:00:00 and 23:59:59, being 00:00:00 means the trade was
> done at midnight which may well not be true, consider settment date
> where the trade needs to settle on a particular day, not by midnight but
> sometime on that day.
>
> In SQL Server because we don't have a DATE data type we need to store
> dates as an integer in the form yyyymmdd which is unfortunate but the
> reality.[/color]

Only if you like to throw out babies and bathtubs simultaneously.

All our settledate columns are datetime, or more precisely aba_date,
which is a datetime with a rule bound to that screams blue murder if
you try to use anything else than 00:00:00.000 for the time porttion.

And, no, our customers do not settle at midnight.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tony Rogerson]

Re: Problem with using BETWEEN for date range...

> All our settledate columns are datetime, or more precisely aba_date,[color=blue]
> which is a datetime with a rule bound to that screams blue murder if
> you try to use anything else than 00:00:00.000 for the time porttion.
>
> And, no, our customers do not settle at midnight.[/color]

Presumeably you also have a computed column that strips away the time
component so only the date gets passed back to the client?

To quote celko, using datetime to hold a pure date is really a kludge ;),
seriously though, even though you have a check constraint to prevent times
over than midnight, it shouldn't have a time at all and any query result
should not contain a time component, it ensinuates that settment date is not
a date but actually a date with a time, so to the application developer or
third party vendor accessing your webservice etc... it might well appear
that trades should be settled by midnight.

All the trading systems I've worked with ALL use the integer data type to
hold pure dates.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97B9F36 F1FE35Yazorman@ 127.0.0.1...[color=blue]
> Tony Rogerson (tonyrogerson@s qlserverfaq.com ) writes:[color=green]
>> Yes but hang on a minute, what if you don't want a time, holding the
>> time component is misleading that indicates the data value may contain a
>> time between 00:00:00 and 23:59:59, being 00:00:00 means the trade was
>> done at midnight which may well not be true, consider settment date
>> where the trade needs to settle on a particular day, not by midnight but
>> sometime on that day.
>>
>> In SQL Server because we don't have a DATE data type we need to store
>> dates as an integer in the form yyyymmdd which is unfortunate but the
>> reality.[/color]
>
> Only if you like to throw out babies and bathtubs simultaneously.
>
> All our settledate columns are datetime, or more precisely aba_date,
> which is a datetime with a rule bound to that screams blue murder if
> you try to use anything else than 00:00:00.000 for the time porttion.
>
> And, no, our customers do not settle at midnight.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

[Erland Sommarskog]

Re: Problem with using BETWEEN for date range...

Tony Rogerson (tonyrogerson@s qlserverfaq.com ) writes:[color=blue]
> Presumeably you also have a computed column that strips away the time
> component so only the date gets passed back to the client?[/color]

Computed column? Hey, this system started its life in SQL Server 4.x. And
you cannot accuse for being too quick to leave SQL 6.5. I think we have
two computed columns in the system.

Yes, the time portion is a bit of noice in Query Analyzer. For the client,
it is a non-issue.
[color=blue]
> All the trading systems I've worked with ALL use the integer data type to
> hold pure dates.[/color]

One of our competitors seems to go by char(8). (They're not using SQL
Server. Their system runs on AS 400, so I guess it's DB2.) We have taken
over several of their customers and converted their data to our system.
I've seen some completely hilarous dates there.

Using integer or char(8) for dates is in my opinion an even worse solution,
as you run the risk of getting dates like 20060230 and that in the system.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tony Rogerson]

Re: Problem with using BETWEEN for date range...

> Using integer or char(8) for dates is in my opinion an even worse[color=blue]
> solution,
> as you run the risk of getting dates like 20060230 and that in the system.[/color]

Well not really, its the same argument i'd have with a time component of the
datetime, both are fixed with a check constraint.

I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount to
a lot more IO for larger trading systems.....

check( ISDATE( 20060230 ) = 1 ) which is simpler then the check constraint
needed for slicing off / checking for the time component!

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97BA66A F76B25Yazorman@ 127.0.0.1...[color=blue]
> Tony Rogerson (tonyrogerson@s qlserverfaq.com ) writes:[color=green]
>> Presumeably you also have a computed column that strips away the time
>> component so only the date gets passed back to the client?[/color]
>
> Computed column? Hey, this system started its life in SQL Server 4.x. And
> you cannot accuse for being too quick to leave SQL 6.5. I think we have
> two computed columns in the system.
>
> Yes, the time portion is a bit of noice in Query Analyzer. For the client,
> it is a non-issue.
>[color=green]
>> All the trading systems I've worked with ALL use the integer data type to
>> hold pure dates.[/color]
>
> One of our competitors seems to go by char(8). (They're not using SQL
> Server. Their system runs on AS 400, so I guess it's DB2.) We have taken
> over several of their customers and converted their data to our system.
> I've seen some completely hilarous dates there.
>
> Using integer or char(8) for dates is in my opinion an even worse
> solution,
> as you run the risk of getting dates like 20060230 and that in the system.
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

[Erland Sommarskog]

Re: Problem with using BETWEEN for date range...

Tony Rogerson (tonyrogerson@s qlserverfaq.com ) writes:[color=blue]
> I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount
> to a lot more IO for larger trading systems.....[/color]

So that is an argument that carries some weight.

But integer data for dates is so much messier to deal with, not the
least for the client.
[color=blue]
> check( ISDATE( 20060230 ) = 1 ) which is simpler then the check
> constraint needed for slicing off / checking for the time component![/color]

Nah, I would write it as

CREATE RULE aba_date_rule (isdate(ltrim(s tr(@x))) = 1

I hate to rely on implicit conversions from integer to string.

(And it is a rule, as I only want it one place.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Alexander Kuznetsov]

Re: Problem with using BETWEEN for date range...

Erland,

There is another approach, you can number days sequentially, such as
datediff(day, '20000101', your_original_d ate). In many cases it allows
to use a 2-byte integer, and that may be quite a difference. I use
dateadd to get dates from those integers, but but dates are needed for
display purposes only, not frequently. I also use a calendar table with
the the same integer as PK.

[Tony Rogerson]

Re: Problem with using BETWEEN for date range...

Make sure @x is nvarchar then, because thats what ISDATE takes.
[color=blue]
> But integer data for dates is so much messier to deal with, not the
> least for the client.[/color]

I totally agree, but its more messy have the time component on queries in my
experience.

Having said that i always convert the integer to a smalldatetime when
creating a fact table purely because its easier for the time dimension on my
dimensional model.....

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97BAED6 B51AAAYazorman@ 127.0.0.1...[color=blue]
> Tony Rogerson (tonyrogerson@s qlserverfaq.com ) writes:[color=green]
>> I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount
>> to a lot more IO for larger trading systems.....[/color]
>
> So that is an argument that carries some weight.
>
> But integer data for dates is so much messier to deal with, not the
> least for the client.
>[color=green]
>> check( ISDATE( 20060230 ) = 1 ) which is simpler then the check
>> constraint needed for slicing off / checking for the time component![/color]
>
> Nah, I would write it as
>
> CREATE RULE aba_date_rule (isdate(ltrim(s tr(@x))) = 1
>
> I hate to rely on implicit conversions from integer to string.
>
> (And it is a rule, as I only want it one place.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]