SQL searching between dates with MYSQL

Classic Servoy
1

HI,
I have the following sql statement to search between 2 dates but it does not include the acual start date and end date, which I guess makes sense but how do you include these dates?

Where
	(sales_rep_line_items.sales_order_date Between "2009-08-01 00:00:00" and "2009-08-31 00:00:00"
    And sales_rep_line_items.sales_repid = 48)

do I need to include a ‘AND’ statement for both dates ?? :?

2

OK,
I have worked this one out, timedate stamps are a pain on autoenter columns in servoy, is there anyway to change this behaviour only to include the date component and zero out the time component ??? :D

3

Hi Phillip,

I guess you can get the date from a timestamp using the MySQL date() function.
I am surprised you don’t get your date that includes those dates because the MySQL reference manual tells us it should include the left and right date in the range.

Also what do you mean with “timedate stamps are a pain on autoenter columns in servoy”? What is making it a pain ?

4

ROCLASI:
Hi Phillip,

I guess you can get the date from a timestamp using the MySQL date() function.
I am surprised you don’t get your date that includes those dates because the MySQL reference manual tells us it should include the left and right date in the range.

Also what do you mean with “timedate stamps are a pain on autoenter columns in servoy”? What is making it a pain ?

Hi Robert,
Thanks for the reply,

The problem is that I only want to search on date not datetime, if you use servoy’s autoenter function to datestamp a record automatically it includes both time and date, this is a pain as there is no option to enter date only. My problem is that when using the following statement

Where
sales_rep_line_items.sales_order_date Between "2009-08-01 00:00:00" and "2009-08-31 00:00:00"

Mysql will only return the start and end dates if the time stamp matches not just the dates. Everything between the dates is fine just not the first and last.

I have found that if you use the datepicker manually on a form and return the selected date to a dataprovider the time has been ignored, which is what I would have expected from Servoys own autoenter process.

I now have a workaround, but I can see that I will need to be carefull with using dates and autoenter if they are to be searched on.

var vDateString = utils.dateFormat(someDate,'yyyy/MM/dd 00:00:00');
someColumn = new Date(vDateString);
5

Phillip - I’ve not tried this yet but my understanding is that you can use a MySQL ‘Date’ column type instead of the the ‘DateTime’ column type. Servoy will treat them the same but all the time component will be zero for data entry and for data reading???

Obviously depends on if you’ll need the time component later for something else!

6

HI,
Thanks for the reply, I did consider doing this but was unsure of the consequences to the rest of my solution searching on dates.

Anyway I am now aware of the problem and will be more careful.

Many Thanks

7

Hi Phillip,

What I meant was to convert the column to a date with the Date() function. Like so:

Where
Date('yyyy-mm-dd',sales_rep_line_items.sales_order_date) Between "2009-08-01" and "2009-08-31"

Not sure if the format is correct but you get the idea.

Hope this helps.