Home General
New Blog Posts: Merging Reports - Part 1 and Part 2

Dates on SQLite

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

Comments

  • 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
  • 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.
Sign In or Register to comment.