Thanks Igor. It works fine.
With Regards Karthick V "The secret of being miserable is to have leisure to bother about whether you are happy or not. The cure for it is occupation." George Bernard Shaw (1856-1950) -----Original Message----- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Friday, November 17, 2006 6:37 PM To: SQLite Subject: [sqlite] Re: Date and time comparison Karthick V - TLS , Chennai <[EMAIL PROTECTED]> wrote: > I need to get the row id for a time which falls within the start and > end time. > > I am using this query. > > select RowID, > strftime('%Y-%m-%dT%H:%M:%S',starttime), > strftime('%Y-%m-%dT%H:%M',endtime) from History where > strftime('%Y-%m-%dT%H:%M',starttime) < > strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17') and > strftime('%Y-%m-%dT%H:%M',endtime) > > strftime('%Y-%m-%dT%H:%M','2006-11-17T12:17'); > > This works well if there is only one matching row. > > However if more than one row matches, I need to get the row whose > start time is closest to the given time. Just add order by strftime('...', starttime) desc limit 1; Observe that, since all starttimes are before the given time, the nearest one is also the latest one. Igor Tandetnik ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - DISCLAIMER The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
