Re: Converting a date to a string

On 22 Aug 1998, Bob Neidecker wrote: > How do I convert a date to a string so that I can have the following where > clause > > WHERE date_column LIKE '12/%1/98' I'm assuming this is an American format date. What you're asking for is all dates in the month December 1998 (unless that's 1898, or 2098 -- use 4-digits for the year, dammit; the Y2K bug will be hitting you very soon). If so, a better way of phrasing the query would be: WHERE date_column BETWEEN MDY(12,1,1998) AND MDY(12,31,1998) because that should be able to use any index on date_column (and it a more explicit way of asking the question). > I want users to be able to use wildcards in strings. I know there is a > performance hit. Since you cannot do a like on a date column you must > first convert it to a string using some sort of string conversion operator. > I am new to Informix but in Oracle the where clause would look like > > WHERE TO_CHAR(date_column ,'MM/DD/YYYY') LIKE '12/%1/1998'. > > In sybase it would look like > > WHERE convert(char(10), date_column, 101) LIKE '12/%2/1998' As Clem commented, there is a TOCHAR function in 7.3 IDS which you could use, but don't come shouting about poor performance as the database does a sequential scan on the date_column. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn