Creating a "BETWEEN dates" SQL query...

E

EFileTahi-A

Well-known member
Joined
Aug 8, 2004
Messages
539
Location
Portugal / Barreiro
Am trying to retrieve records on my Access database between dates, but I just cant...

The Access database have a field named "dataagend" with the "Short date" format (dd-MM-yyyy)

So basically, the SQL query would be like this:
"SELECT * FROM historico WHERE dataagend BETWEEN 01-01-2005 AND 12-12-2005"

But it does not work, I always get the following error msg: "Data type mismatch in the criteria expression"

If I remove the single quotes () it does not give me an error, but it also returns me no record...

Please aid, thanks!
 
PlausiblyDamp said:
IIRC you need to enclose dates inside # characters.
Click to expand...

I tryed with the "#" but it does not return no record... I guess the prob is elsewhere...

"SELECT * FROM historico WHERE dataagend BETWEEN #01-01-2005# AND #12-12-2005#"
 
Well it does work, but badly...

First, Access assumes the 2 first numbers of date as the Month, instead of Day and am using dd-MM-yy format on Access config, yet, it does not matchs nothing if I lower the year... exp:

I have some records with todays date (06-02-05), so, in order to an access query return me any of these records i have to say BETWEEN 02-02-2005 AND xx-xx-2005 other value, but If i put BETWEEN xx-xx-2004 AND xx-xx-2006 he retrieves nothing...

God damn this crap!
 
EFileTahi-A said:
God damn this crap!
Click to expand...

Unfortunately, the SQL-99 standard defines dates as being manipulated in the American style of mm/dd/yyyy. As SQL is a text-only language, and can be input on a client in one regional setting, to be run on a server in another regional setting, there needed to be a standard method of referring to dates. In this case, they decided that the US method would be fine for the standard.

I work around this using a couple of methods:

1. Use parameters.
This will allow you to manipulate the dates using objects that are passed into your sqlCommand

2. Have date conversion functions
I have a couple of functions, one most notably DateToSql which allows me to pass in dates in various formats (as a DateTime, text string etc.) and will format appropriately based upon the server-type in use. I use this function for when I absolutely need the raw-speed of issuing an SQL command directly, and not have the associated overhead of parameters (which is absolutely miniscule in most cases)

B.
 
I had the same problem, the way I got round it was to use the DateValue function ie

Code: 
"SELECT * FROM historico WHERE dataagend >= DateValue(01-01-2005)  AND dataagend <= DateValue(12-12-2005)"
 
EFileTahi-A

I tried a start date of 31/12/2003 and end date of 14/02/2005 and got everything for 2004 plus this years, so not sure why it does not work for you.

Can you show me what your sql statement is and what your start and end dates are and I will try them here.
 
pendragon said:
EFileTahi-A

I tried a start date of 31/12/2003 and end date of 14/02/2005 and got everything for 2004 plus this years, so not sure why it does not work for you.

Can you show me what your sql statement is and what your start and end dates are and I will try them here.
Click to expand...

I tryed the same query you post few topics ago:

Code: 
"SELECT * FROM historico WHERE dataagend >= DateValue(01-01-2005)  AND dataagend <= DateValue(12-12-2005)"

Jesus! Is this voodoo or what? Anyway, what kind of fields on the Access Database are you using pendragon? DateTime types?
 
Can you zip up your code and database, or a sample? The query you have (with the #) should work fine if the table is defined as expected.

-ner
 
Nerseus said:
Can you zip up your code and database, or a sample? The query you have (with the #) should work fine if the table is defined as expected.

-ner
Click to expand...


Well, the program is kinda big, yet, you will need Xceed, I will try to create a small program using the same database so things can be easier... But, if you have Xceed and do not mind it all, let me know about it
 
EFileTahi-A

Yes I am using DateTime Fields.

This is the string I have been testing with

Code: 
SELECT * FROM POPHeader WHERE POPDate >= DateValue(31/12/2003) AND POPDate <= DateValue(14/02/2005) ORDER BY POPOrdNo

This is probably a stupid question, but I am running out of ideas, the machine you are running on does have its local settings for the date set to the format that you are searching on as the DateValue function uses them.
 
hope it helps

i had the same problem and used this code to over come it.

Dim myReport As New CrystalReport1()

myReport.DataDefinition.RecordSelectionFormula = "{late_amends.Sent} = (CDateTime (" & (DateTimePicker1.Value.ToShortDateString) & ") to CDateTime (" & (DateTimePicker2.Value.ToShortDateString) & ")) "

CrystalReportViewer1.ReportSource = myReport

hope it helps
 
@falcon: I dont think he has a problem with Crystal reports, just with straight-up SQL.

@EFileTahi-A: I was hoping youd have already written a small test app to test your query. If something doesnt work for me as I think it will, I try and write a smaller version to test out why and to experiment. See attached for a sample project I wrote. The included Access database has one table named "historico" and two fields. The code is C# but should be straightforward and pretty small.

I dont have XCeed so that part wouldnt work for me. Im not sure what part of XCeed you are using but it sounds like your problem is with straight ADO.NET running a query.

Heres the query I used. I used the date format "mm/dd/yyyy" although with all of your tests (in the other posts) you always have the day and month being the same day, so it wont really matter :)

SELECT * FROM historico WHERE dataagend between #01-01-2005# AND #12-12-2005#

-ner
 

Attachments

Nerseus said:
@falcon: I dont think he has a problem with Crystal reports, just with straight-up SQL.


@EFileTahi-A: I was hoping youd have already written a small test app to test your query. If something doesnt work for me as I think it will, I try and write a smaller version to test out why and to experiment. See attached for a sample project I wrote. The included Access database has one table named "historico" and two fields. The code is C# but should be straightforward and pretty small.
Click to expand...

I did wrote a small program to teste it.. and it does not work

Heres the query I used. I used the date format "mm/dd/yyyy" although with all of your tests (in the other posts) you always have the day and month being the same day, so it wont really matter :)
SELECT * FROM historico WHERE dataagend between #01-01-2005# AND #12-12-2005#
Click to expand...

I have 2 DateTimePicker boxes on a form to let the user select dates between 1900 to 2999 and retrieve all correspoding records. What I do is storing both dateTimePicker objects values in 2 saperate strings var. Then I construct the query using the vars in place the xx-xx-xxxx, that is:

Code: 
"...between #" + myStringVar1 + "#..."

Now, I have 1 major and 1 minor problem... The minor one is that using the way u described Nerseus, SQL will swanp month and day values, not really a problem since I can solve this by adding some code, but the Major problem is this query does not return values between years as I described above... the pendragon method seems to work around the "minor" prob... but not the "major" prob, and pendragon also says he does not have the "between years" issue, which make me believe that the prob is elsewhere...

I will try to upload my small program and DB this weekend. I dont know what to do next...

But I do know that I really apreciate all who post here to try to help me out! Thank you everyone!
 
Back
Top