Oninit cdi / Archive
Talk to an expert →

select * from calls where timestamp > ?????

Posted in 2007

Summary

cj needed to compare a DATETIME YEAR TO SECOND column against a specific date and time. Doug Lawry showed the default literal format, such as '2007-07-06 07:42:34', and pointed to TO_DATE for other formats on version 9 or later. Superboer added casting date() to datetime year to second plus hour, minute and second units, or an SPL. cj confirmed it worked.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

cj — — source: Usenet: comp.databases.informix

timestamp is a datetime year to second field. I need to specify a time like 6/7/2007 7:42:34am. How can I accomplish this? The closest I've been able to get is select * from calls where timestamp > date("06/07/2007") but I need the time too.

↪ replying to cj
Doug Lawry — — source: Usenet: comp.databases.informix

The default DATETIME format is as follows: select * from calls where timestamp > '2007-07-06 07:42:34' If you are using version 9 or later and need to use a different format, look up the TO_DATE function in the SQL Syntax manual or InfoCenter. -- Regards, Doug Lawry www.douglawry.webhop.org "cj" <ChrisJones@MailShack.com> wrote in message news:1181567460.303158.102590@p47g2000hsd.googlegroups.com... > timestamp is a datetime year to second field. I need to specify a > time like 6/7/2007 7:42:34am. How can I accomplish this? > > The closest I've been able to get is > > select * from calls where timestamp > date("06/07/2007")> > but I need the time too.

↪ replying to Doug Lawry
cj — — source: Usenet: comp.databases.informix

Thank you very much! On Jun 11, 10:16 am, "Doug Lawry" <l...@nildram.co.uk> wrote: > The default DATETIME format is as follows: > > select * from calls where timestamp > '2007-07-06 07:42:34'> > If you are using version 9 or later and need to use a different format, look up > the TO_DATE function in the SQL Syntax manual or InfoCenter. > > -- > Regards, > Doug Lawrywww.douglawry.webhop.org > > "cj" <ChrisJo...@MailShack.com> wrote in message > > news:1181567460.303158.102590@p47g2000hsd.googlegroups.com... > > > timestamp is a datetime year to second field. I need to specify a > > time like 6/7/2007 7:42:34am. How can I accomplish this? > > > The closest I've been able to get is > > > select * from calls where timestamp > date("06/07/2007")> > > but I need the time too.

↪ replying to cj
Superboer — — source: Usenet: comp.databases.informix

> select * from calls where timestamp > ( date("06/07/2007") :: datetime year to second) + 7 units hour + 42 units minute + 34 units second .... in case i mix up am and pm, i usually do.... > select * from calls where timestamp > ( date("06/07/2007") :: datetime year to second) + 19 units hour + 42 units minute + 34 units second or write an spl which takes a string and returns an int which represents the nr of seconds for hour minute sec. Superboer. On 11 jun, 15:11, cj <ChrisJo...@MailShack.com> wrote: > timestamp is a datetime year to second field. I need to specify a > time like 6/7/2007 7:42:34am. How can I accomplish this? > > The closest I've been able to get is > > select * from calls where timestamp > date("06/07/2007")> > but I need the time too.