Re: SELECT * FROM WHATEVER WHERE DATETIME(invc_date) YEAR TO DAY = ??

>From: merlyn@panix.com (Paul Fabozzi) >Subject: SELECT * FROM WHATEVER WHERE DATETIME(invc_date) YEAR TO DAY = ?? >Date: 28 Nov 1994 13:18:39 -0500 >X-Informix-List-Id: <news.10023> > >I would like to do a select on a date field which will include all >records for a certain month. So I define a DATETIME YEAR TO MONTH >Variable, and I want to compare this to a date field in my table. > >I cant figure the syntax, I tried > >SELECT * FROM invc_header WHERE (DATETIME (invoice_date) YEAR TO MONTH) =>"1994-11" You are confusing DATETIME, which takes a DATETIME literal argument, with EXTEND, which converts between DATETIME types, or between DATE and DATETIME. Try: SELECT * FROM invc_header WHERE (EXTEND(invoice_date, YEAR TO MONTH) = "1994-11"except you use your variable name... Actually, if invoice_date is a DATE, I would probably use: SELECT * FORM invc_header WHERE MONTH(invoice_date) = 11 AND YEAR(invoice_date) = 1994 using variables as appropriate. >The "1994-11" should be able to be a variable (DATETIME YEAR TO MONTH) > >But it gives me syntax errors. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>