Re: Date Problem

I would start by actually declaring your field as date, not char(9). I can se no reason why you would want to use char(9) for a date value. Even though your customer have suggested it, I wouldn't accept it unless they have a very powerfull reason. And char(9)? The date format is then what? (30.01.96 is 8 characters. What's the 9th used for, and what about year 2000?) bflams@uranium.bfl.soft.net (Amita Shrivastava) wrote: :Hi Jonathan : :I am sorry I didn't give you complete picture. : :Well this problem we are facing only at our Customer place. I only have what :they have sent me. I'll reproduce it again. [You are right INTO comes before :FROM and after SELECT..sorry..]. : :1. If a field is declared as DATE in a table, and when we read it from :table to a program variable, the program variable is NULL. But it isn't. Further down you say it's declared as char(9) which is not a date. Or is it in this test? :select f_date into :ws_date from tableA : :In this query ws-date is NULL eventhouh f_date has some value. : :2. : :/* Declare Cursor to get Trailer Details */ : EXEC SQL : DECLARE trailer_location CURSOR FOR : SELECT tl_carrier, tl_trailer_no, tl_dflag, tl_truck_type,tl_date_emptied, : trail_rep.cur_row = 6; : : create_line ( trail_bodywin, 2, 1, 75 ); :/* Call Initialise Screen */ : initialise_trailer_loc ( trail_bodywin ); : create_line ( trail_bodywin, 5, 1, 75 ); : :/* Declare Cursor to get Trailer Details */ : EXEC SQL : DECLARE trailer_location CURSOR FOR : SELECT tl_carrier, tl_trailer_no, tl_dflag, tl_truck_type,tl_date_emptied, : tl_time_emptied, tl_arrival_date, tl_arrival_time : FROM : truckmaster : WHERE : date(tl_arrival_date) BETWEEN date(from_date) AND date(to_date); And this where clause can't use an index because of the date function on both sides. That's bad. Your customer realy have to have a good reason for wanting dates in char(9) fields. : EXEC SQL : OPEN trailer_location; : if ( sqlca.sqlcode != 0 ) : { : .................. : :tl_arrival_date is defined as char(9) in the table.[This was suggested by :our Customer] : :if This is the condition the date( tl_arrival_date ) is NULL. : :From these queries it is very clear that date as data type and date function :as such is not a problem. Where is the problem ? Should we define a date :field as char(9) at all? : :Please help. : :Background is - INFORMIX online 5.05, ESQL/C running on AIX 3.2.5 : :Thanks : :Amita : : The clock is running, make the most of today....... : Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org