Dates on SQLite

perroabel

Hello

I'm trying to create a simple customer report with a search datetime field between parameter, however I don't want reportbuilder ask for time, just to dates: "from" and "to".

How can I achieve this? I am using SQLite as database

It seems the only way is to create the next sentence: SELECT * FROM mytalbe WHERE DATE_FORMAT(date,'%Y-%m-%d') BETWEEN '2014-10-09' AND '2014-10-10'

I want to solve this on design time, I don't want to create any report using runtime events.... is it possible?

Thanks

perroabel

The idea is that maybe I can put a date "parameter" on the "design tab" so when I invoke the report it ask the user two different date on a "calendar" control, but report builder must to convert the selected dates to a string values and then fill the SQL sentence with something like this:

SELECT *
FROM mytable
WHERE DATE_FORMAT(date,'%Y-%m-%d')
BETWEEN '2014-10-09' AND '2014-10-10' <-- how reportbuilder convert the selected user dates from
calendar control to string values in such formatted string

calara

Most databases will just add zero for the time component if you just give a date to DateTime field. So when the above gets to your database the search criteria will be

'BETWEEN 2014-10-09 00:00:000 AND 2014-10-10 00:00:000'

ie - you'll miss any data on the last day with any time after midnight.

I generally deal with this in report builder by using an tdaSQLBuilder object in Report.BeforeOpenDataPipelines where I can adjust the second parameter to '2014-10-10 23:59:59' (although I think it's easier to read with >= AND <=). It's a pain to have to remember but works seamlessly.