RE: Date Problem
Dear Amita!
Your problem is that whenever the date value from the date field is
assigned to a CHAR variable, it will be in the format of your DBDATE
environment variable including the separator (ie. mm/dd/yyyy that makes
10 spaces and your variable only has 9 spaces).
Example:
if MICKEY is defined as CHAR(09) (As your customer Suggested?, Mmm, if I
Design a Database, I will not let anyone who does not know a bit of
what I am doing, to tell me how to do my JOB)
and PLUTO is defined as DATE in the table DISNEY, then whenever you
select pluto from disney, MICKEY will experiment an OVERFLOW!!!,
So, please check the SIZE of your CHAR variable.
Oh, I almost forgot, GOTO TO CHARLES`s trainning class, even if I do not
Know who is CHARLES :=)
Regards :)
-----------------------------------------------------------------------
Mario Estrada / SISTECO, S.A.
INFORMIX, /Phone (502) 3340214
Support Department /Fax(502) 3348447
----------------------------------------------------------------------
----------- Reply Separator-----------
----------
De: Amita Shrivastava[SMTP:bflams@uranium.bfl.soft.net]
Enviado: jueves 30 de enero de 1997 12:07
Para: informix-list@rmy.emory.edu; johnl@informix.com
Asunto: Date Problem
Hi Charles
I have posted this query in the Informix Newsgroup. Can you help me?
[You don't miss a chance to sell your trainings..yeah!!!] I have forwarded
the mail to my boss. I personally hope this training was in Bangalore.
Regards
Amita
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.
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);
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.......