Re: datetime and date combine

jjdai@hotmail.com wrote: > I have a question during my developing , > I have two columns in a table , the data type of the first(column A) > is DATE > and another ( column B ) is DATETIME HOUR TO MINUTE , > the column A means the date that the data inserted , > the column B means the time that the data inserted , > > now , I want to develop a program that user can entry a start > date(in the form file it is defined as DATE type )and time (it is defined > as DATETIME HOUR TO MINUTE type ) and then entry a finish date and time > ,the program will search out the records inserted between the user defined > duration , > > but when I write the program I can not convert the date and datetime type , > and can not compose the query statement . > > can you give me any advice . Well, you did make life complicated for yourself with the design. You also didn't tell us which language you're using -- so we can't really answer... However, we can suggest various different possibilities. I'm going to assume you are using something like I4GL INPUT statements (not CONSTRUCT), and that the start and end date/time values are entered into a DATETIME YEAR TO MINUTE variables t1 and t2, and that t1 <= t2. 1. Write the SELECT statement in terms of the variable values: SELECT * FROM SomeWhere WHERE EXTEND(ColumnA, YEAR TO MINUTE) + (ColumnB - DATETIME(0:0) HOUR TO MINUTE) BETWEEN t1 AND t2 Note that you have to convert the time in ColumnB into an interval before you can add it to the extended value of ColumnA. Also note that I've not checked the detailed syntax, so there could be bugs above. 2. Split the values t1 and t2 into components t1_d, t1_t, t2_d, t2_d and write the query in terms of those. SELECT * FROM SomeWhere WHERE (ColumnA > t1_d OR (ColumnA = t1_d AND ColumnB >= t1_t)) AND (ColumnA < t2_d OR (ColumnA = t2_d AND ColumnB <= t2_t)) Again, I've neither checked the syntax nor that the logic really does what I think it does -- I'm giving out ideas, not answers. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>