santoshv25
asked on
conversion from interval to decimal
i am calculating difference between two dates(datetime) declared as data type
and putting the value to decimal field
it is giving conversion type error
how to achieve this ?
and putting the value to decimal field
it is giving conversion type error
how to achieve this ?
ASKER
sprintf(f_upd_qry,"update scr_ship_rec \
\nset elapse_hours = case \
\n when nc_dt is not null \
\n then \(\(nc_dt - pickup_dt\) * 24\) end");
and elapse_hours is declared as decimal in the table
\nset elapse_hours = case \
\n when nc_dt is not null \
\n then \(\(nc_dt - pickup_dt\) * 24\) end");
and elapse_hours is declared as decimal in the table
use difftime() to calc. the difference b/w 2 date-times i.e. time_t structures.
it returns the no of seconds as a double value
it returns the no of seconds as a double value
Hi santoshv25,
Assuming that nc_dt and pickup_dt are C variables, try this:
sprintf(f_upd_qry,"update scr_ship_rec \
\nset elapse_hours = case \
\n when nc_dt is not null \
\n then \(%d\) end", nc_dt - pickup_dt * 24);
Kent
Assuming that nc_dt and pickup_dt are C variables, try this:
sprintf(f_upd_qry,"update scr_ship_rec \
\nset elapse_hours = case \
\n when nc_dt is not null \
\n then \(%d\) end", nc_dt - pickup_dt * 24);
Kent
If your date is stored in struct tm structure,use mktime to convert it to time_t.
ASKER
nc_dt and pickup_dt variables are declared as type of datetime year to second
i will try the method and let u know
i will try the method and let u know
ASKER
the logic of %d is not working
because nc_dt and pickup_dt are field variables of the table scr_ship_rec
any other solutions ?
because nc_dt and pickup_dt are field variables of the table scr_ship_rec
any other solutions ?
what type are they?
Are they a C date-time construct?
either time_t or struct tm.
Are they a C date-time construct?
either time_t or struct tm.
ASKER
these two fields are part of a structure in my program
under create scratch table <tablename>
{ nc_dt datetime year to second,
pickup_dt datetime year to second
}
under create scratch table <tablename>
{ nc_dt datetime year to second,
pickup_dt datetime year to second
}
ASKER
any ideas/solutions ?
i need this immediately.....very urgent requirement
i need this immediately.....very urgent requirement
so, nc_dt and pickup_dt are variables of your own defined datetime structure?
If that is the case,you cant subtract two struct variables directly.
If that is the case,you cant subtract two struct variables directly.
ASKER
then ?
how to achieve this ?
2 datetime variables substraction and put the output value to a decimal field in the same table
how to achieve this ?
how to achieve this ?
2 datetime variables substraction and put the output value to a decimal field in the same table
how to achieve this ?
You'll have to implement your own function to subtract each field and calc. the difference
to the resolution(minutes,seconds ) you want.
You could set your values to struct tm structure and use difftime().
to the resolution(minutes,seconds
You could set your values to struct tm structure and use difftime().
ASKER
could u give me how to do that ?
in detail ?
in detail ?
int main(void)
{
struct tm time_check;
time_t dt1;
int year=1980, month=4, day=23;
/* load the time_check structure with your data */
time_check.tm_year = year - 1900;
time_check.tm_mon = month - 1;
time_check.tm_mday = day;
time_check.tm_hour = 0;
time_check.tm_min = 0;
time_check.tm_sec = 1;
time_check.tm_isdst = -1;
/* call mktime to fill in the weekday field of the structure */
dt1=mktime(&time_check);
//similarly for the 2nd date
//then
printf("Diff:%f",difftime( dt2,dt1));
return 0;
}
{
struct tm time_check;
time_t dt1;
int year=1980, month=4, day=23;
/* load the time_check structure with your data */
time_check.tm_year = year - 1900;
time_check.tm_mon = month - 1;
time_check.tm_mday = day;
time_check.tm_hour = 0;
time_check.tm_min = 0;
time_check.tm_sec = 1;
time_check.tm_isdst = -1;
/* call mktime to fill in the weekday field of the structure */
dt1=mktime(&time_check);
//similarly for the 2nd date
//then
printf("Diff:%f",difftime(
return 0;
}
ASKER
so u mean to say that the field nc_dt and pickup_dt has to be broken into year/month and day
but these will be stored as date + seconds/minutes in the field
how to break it up ?
but these will be stored as date + seconds/minutes in the field
how to break it up ?
What is the format in which the date is stored in the nc_dt and pickup_dt?
You can specify the hours,minutes and seconds also in
time_check.tm_year = year - 1900;
time_check.tm_mon = month - 1;
time_check.tm_mday = day;
//from here
time_check.tm_hour = hour;//declare hour,min,sec as int and set here
time_check.tm_min = min;
time_check.tm_sec = sec;
//to here
time_check.tm_isdst = -1;
You can specify the hours,minutes and seconds also in
time_check.tm_year = year - 1900;
time_check.tm_mon = month - 1;
time_check.tm_mday = day;
//from here
time_check.tm_hour = hour;//declare hour,min,sec as int and set here
time_check.tm_min = min;
time_check.tm_sec = sec;
//to here
time_check.tm_isdst = -1;
ASKER
the format how the date is stored is :
2004-02-04 11:14:00
2004-02-04 11:14:00
>the format how the date is stored is :
>2004-02-04 11:14:00
IS this stored as a string(char array).
>2004-02-04 11:14:00
IS this stored as a string(char array).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try NVL instead:
update scr_ship_rec
set elapse_hours = NVL((nc_dt - pickup_dt) * 24 , NULL);
update scr_ship_rec
set elapse_hours = NVL((nc_dt - pickup_dt) * 24 , NULL);
Post your code.
Kent