Dates on SQLite

perroabel · January 30

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 · January 30

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 · February 27

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.

Dates on SQLite

Comments