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

merlyn@panix.com (Paul Fabozzi) writes: >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" >The "1994-11" should be able to be a variable (DATETIME YEAR TO MONTH) Try: SELECT * FROM invc_header WHERE EXTEND(invoice_date, YEAR TO MONTH) = "1994-11" or: SELECT * FROM invc_header WHERE EXTEND(invoice_date, YEAR TO MONTH) = EXTEND("1994-11", YEAR TO MONTH) In the first example, "1994-11" could be a column of type DATETIME YEAR TO MONTH, and in 4GL/ESQL I'm sure a variable of the appropriate type would work fine. In the second example, you could of course substitute a variable/column of type DATE or CHAR even. Using DATETIME in the context you were trying is ... well, it's just plain wrong. See the SQL Reference manual pages 3-20 and up for more details. ======================================================================= Dennis J. Pimple dennisp@informix.com Opinions expressed Senior Consultant -------------------- are mine, and do not Informix Software Inc Voice: 303-850-0210 necessarily reflect Denver Colorado USA Fax: 303-779-4025 those of my employer.