Creating a "BETWEEN dates" SQL query...

[EFileTahi-A]

Am trying to retrieve records on my Access database between dates, but I just can't...

 

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]

IIRC you need to enclose dates inside # characters.

[EFileTahi-A]

IIRC you need to enclose dates inside # characters.

 

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#"

[EFileTahi-A]

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!

[PlausiblyDamp]

Try using a parameterised query rather than concatenating strings to build your SQL.

[EFileTahi-A]

Well, realy don't know what are you talking about, could you show me an example?

[PlausiblyDamp]

http://www.xtremedotnettalk.com/search.php?searchid=121141

[penfold69]

God damn this crap!

 

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.

[pendragon]

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

 

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

[EFileTahi-A]

Ok buddies, thanks for the replies, I will try them as soon as I get home...

 

Pendragon how are you my old friend ;)

[pendragon]

Not to bad EFileTahi-A, finally got the new system up and running at work (not to many bugs :D ) but still not got internet access at home :mad:

[EFileTahi-A]

Pendragon, you way works fine, but only in a specific year, It does not work between years like 2004 and 2006, it won't return me any 2005 record...

[pendragon]

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.

[EFileTahi-A]

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.

 

I tryed the same query you post few topics ago:

 

"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?

[Nerseus]

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

[EFileTahi-A]

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

 

 

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

[pendragon]

EFileTahi-A

 

Yes I am using DateTime Fields.

 

This is the string I have been testing with

 

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

[/Code]

 

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.

[falcon]

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

[Nerseus]

@falcon: I don't think he has a problem with Crystal reports, just with straight-up SQL.

 

@EFileTahi-A: I was hoping you'd have already written a small test app to test your query. If something doesn't 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 don't have XCeed so that part wouldn't work for me. I'm not sure what part of XCeed you are using but it sounds like your problem is with straight ADO.NET running a query.

 

Here's 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 won't really matter :)

 

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

 

-ner

ADOTestAccess5.zip

[EFileTahi-A]

@falcon: I don't think he has a problem with Crystal reports, just with straight-up SQL.

 

 

@EFileTahi-A: I was hoping you'd have already written a small test app to test your query. If something doesn't 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 did wrote a small program to teste it.. and it does not work

 

Here's 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 won't really matter :)

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

 

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:

 

"...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 don't 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!