2

Just learning SQLite...I run this query:

select guests.[GUEST_NAME],guests.[GUEST_SURNAME],guest_data.[START_DATE],guest_data.[END_DATE]
from guests
INNER JOIN guest_data
ON guests.guest_id=Guest_data.guest_id
order by  guests.[GUEST_SURNAME] ASC

This works OK but I need to learn : How can I get extra field as DAYS as datediff between start_date,end_date ? I tried adding :

 select guests.[GUEST_NAME],guests.[GUEST_SURNAME],guest_data.[START_DATE],guest_data.[END_DATE],DAYS as Daysbetween(guest_data.start_date,guest_data.end_date)

but it will not work. Also how would this query be written in delphi?

user763539
  • 3,509
  • 6
  • 44
  • 103
  • 3
    Have you seen e.g. [`this post`](http://stackoverflow.com/q/289680/960757) ? Btw. this question is not related to Delphi at all. It's a pure SQLite & SQL question... – TLama Oct 20 '13 at 13:03
  • Given SQLite doesn't have a dedicated date type, a key piece of information is how you are storing dates. In a Delphi context, storing as a Delphi TDateTime is tempting; if so, while SQLite's internal date/time functions won't understand the values, you can just take one from the other to get the number of days in between (assuming you aren't dealing with dates around the year 1990). – Chris Rolliston Oct 20 '13 at 13:14

1 Answers1

3
select guests.[GUEST_NAME],guests.[GUEST_SURNAME],guest_data.[START_DATE],guest_data.[END_DATE],
julianday(guest_data.end_date) - julianday(guest_data.start_date) AS DAYS_INTERVAL
from guests
INNER JOIN guest_data
ON guests.guest_id=Guest_data.guest_id
order by  guests.[GUEST_SURNAME] ASC

Please try this structure of syntax and keep us posted. Your interval in days should return as a separate column in the result set named "DAYS_INTERVAL"

Oleg Belousov
  • 9,981
  • 14
  • 72
  • 127
  • works ok .... do you mind if I ask you how can I SUM the DAYS_INTERVAL of particular guest? – user763539 Oct 20 '13 at 13:24
  • In order to apply the aggregation that you have asked about in your comment, try the following: select guests.[GUEST_NAME],guests.[GUEST_SURNAME],guest_data.[START_DATE],guest_data.[END_DATE], SUM(julianday(guest_data.end_date) - julianday(guest_data.start_date)) AS DAYS_INTERVAL from guests INNER JOIN guest_data ON guests.guest_id=Guest_data.guest_id GROUP BY Guest_data.guest_id order by guests.[GUEST_SURNAME] ASC – Oleg Belousov Oct 20 '13 at 13:41
  • Thank you very much Oleg. Much obliged...Now all I need is o translate this into a Delphi SQL query... – user763539 Oct 21 '13 at 06:50