date range

[BJZeak]

another 5 min job gone south

I have a 2k3 Access mdb file and attempting to use 2k8 VB Express to pick up a weeks worth of data starting on Mondays using an SQL statement. My first instinct was to use:

idate >= daStart AND idate <= daEnd

then looking at SQL examples tried:
idate BETWEEN daStart AND daEnd

neither of these appear to do anything

idate is defined as a DATE/TIME field in Access so I explicitly converted every date value using DateValue() which is supposed to return the date part and time as 00:00:00

Where am I going wrong???

----------------------------------
Dim inOffset As Long
Dim daStart as Date
Dim DaEnd as Date
Dim chSelect As String
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=T:\data.mdb")

inOffset = -1 * ((Weekday(Today) + 5) Mod 7)
' 1 2 3 4 5 6 7 1 2 3 4 5 6 7
' s m t w t f s s m t w t f s
' 6 0 1 2 3 4 5
daStart = DateValue(DateAdd(DateInterval.Day, inOffset, Today))
daEnd = DateValue(DateAdd(DateInterval.Day, 6, daStart))
=

chSelect = "Select idate, ref, desc FROM item WHERE ccode = 'ABC' AND datevalue(idate) between " & daStart & " AND " & DaEnd

Dim cmd As OleDbCommand = New OleDbCommand(chSelect, con)
con.Open()
Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim myDataSet As DataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
con.Close()
con = Nothing

 

[BJZeak]

So went at this from a different direction.

inStart = daStart.Year * 1000 + daStart.DayOfYear
inEnd = daEnd.Year * 1000 + daEnd.DayOfYear

chSelect = "Select idate, ref, desc FROM item WHERE ccode = 'ABC' and (datepart(year,idate) * 1000 + datepart(dayofyear,idate)) >= inStart AND (datepart(year,idate) * 1000 + datepart(dayofyear,idate)) <= inEnd "

this appears to not be accepted by the SQL engine??? Datepart is listed as a valid SQL function ... perhaps I didn't format the select statement correctly?

 

[r937]

my advice: don't apply functions to your table date column, otherwise your query cannot use an index on that column and will have to do a table scan

put the column on one side of the operator, and go nuts on the other side

so instead of this --

WHERE
(datepart(year,idate) * 1000 +
datepart(dayofyear,idate)) >= inStart
AND
(datepart(year,idate) * 1000 +
datepart(dayofyear,idate)) <= inEnd

you want to write it like this --

WHERE idate >= some formula
AND idate < some other formula

the first formula should come up with midnight of the monday, while the second formula should come up with midnight of the following monday

because you use midnights, you don't have to worry about DateValue trimming, either

and of course the big secret is that the range test upper end is less than, not less than or equal

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Here is a link comforming what rudy said

http://sqlblog.com/blogs/aaron_bert...s-to-kick-mishandling-date-range-queries.aspx

PluralSight Learning Library

 

[BJZeak]

This is a real mystery ... not sure what is going on.

I follow your index comment and value your correction. Unfortunately your example doesn't work either.

idate >= daStart AND idate < daEnd

I validated that the dates I created; daStart and daEnd are in fact marked at 00:00:00 ... I bumped daEnd to be daStart + 7 too.

However, even if the TIME wasn't 00:00:00 my expectation would be that some records should be returned in the date range. Nothing doing.

The other interesting tidbit is idate >= daStart by itself returns every record from the beginning of time. Which makes absolutely no sence to me.

 

[r937]

the answer will be obvious once we find it, but right now i can't see what's causing the query not to return rows properly

please display the actual values that you're using for daStart and daEnd, and some sample values from the idate column

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[BJZeak]

hmmm ... I wonder ... the examples in the link given by markros appear to wrap the date values in single quotes ... the select string I have is returning

idate >= 25/01/2010 AND idate < 01/02/2010

Perhaps I need to try:

idate >= #25/01/2010# and idate < #01/02/2010#

OR

idate >= '20100125' AND idate < '20100201'

... idate >= #25/01/2010# appears to work but
idate >= #25/01/2010# and idate < #01/02/2010#

returns nothing

 

[r937]

only microsoft access uses dates delimitd with hash marks like #25/01/2010#

sql server uses singe quotes

try like this --

idate >= '2010-01-25' AND idate < '2010-02-01'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[BJZeak]

I tried all variations of 'yyyymmdd' and 'yyyy-mm-dd' ymd, ydm, dmy, mdy ... none of these would run at runtime so went back to using #

I changed the code so it passes in Strings for the dates and this appears to have resolved the problem

chStart = "#" & cstr(daStart) & "#"
chEnd = "#" & cstr(daEnd) & "#"

chSelect = "Select idate, ref, desc " _
& " FROM item " _
& " WHERE ccode = 'ABC'" _
& " AND idate >= " & chStart _
& " AND idate < " & chEnd

Thanx for everyones help

 

[markros]

Are you using Access or SQL Server?

PluralSight Learning Library

 

[BJZeak]

I attempted to use the connection process but found this example on MSDN

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=T:\data.mdb")

so perhaps that means it is using ACCESS?

 

[BJZeak]

Sorry for the grey moment people ... after 35 years of programming one shouldn't expect to make such a simple mistake

retracing my steps I had purposely created my SQL statement with embedded DATE types to ensure the compiler would treat the fields as a DATE ... THEN ... DUH ... I dragged the entire statement out to a STRING ... mistake ONE ... mistake TWO was not recognizing that idate = 01/25/2010 isn't valid in VB... in my defence I have been programming in 4GL for the last 12 years and DATES don't require bracketing so lets just say it has made me lazy.

aqain sorry for taking up your time ... but also thank-you for helping an old fart get back to basics.

 

[r937]

doh!! i think i was unduly influenced by the fact that you posted in the SQL Server forum, and neglected to notice that in your very first post you said you were using Access!!!

so, yeah, you need to delimit your dates with hash marks

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

idate = 01/25/2010

that is valid SQL, but doesn't do what you think. SQL would interpret this as a math statement. It would be interpreted as 1 divide by 25 divide by 2010. Due to integer math, this would evaluate to 0. On SQL server, this would be the same as Jan 1, 1900. In access, I think it's dec 30, 1899. This explains why you were getting an unexpected number of rows.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom