Solved

conversion from interval to decimal

Posted on 2004-04-23
23
200 Views
Last Modified: 2010-05-18
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 ?
0
Comment
Question by:santoshv25
  • 9
  • 8
  • 2
  • +1
23 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 10899110
Hi santoshv25,

Post your code.


Kent
0
 

Author Comment

by:santoshv25
ID: 10899132
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
0
 
LVL 9

Expert Comment

by:ankuratvb
ID: 10899298
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 10899316
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
0
 
LVL 9

Expert Comment

by:ankuratvb
ID: 10899331
If your date is stored in struct tm structure,use mktime to convert it to time_t.


0
 

Author Comment

by:santoshv25
ID: 10899344
nc_dt and pickup_dt variables are declared as type of datetime year to second

i will try the method and let u know
0
 

Author Comment

by:santoshv25
ID: 10899389
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 ?
0
 
LVL 9

Expert Comment

by:ankuratvb
ID: 10899419
what type are they?
Are they a C date-time construct?
either time_t or struct tm.
0
 

Author Comment

by:santoshv25
ID: 10899443
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
}

0
 

Author Comment

by:santoshv25
ID: 10899613
any ideas/solutions ?
i need this immediately.....very urgent requirement
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:ankuratvb
ID: 10899618
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.
0
 

Author Comment

by:santoshv25
ID: 10899640
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 ?
0
 
LVL 9

Expert Comment

by:ankuratvb
ID: 10899678
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().
0
 

Author Comment

by:santoshv25
ID: 10899760
could u give me how to do that ?
in detail ?
0
 
LVL 9

Expert Comment

by:ankuratvb
ID: 10899786
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;
}
0
 

Author Comment

by:santoshv25
ID: 10899870
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 ?

0
 
LVL 9

Expert Comment

by:ankuratvb
ID: 10899927
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;
0
 

Author Comment

by:santoshv25
ID: 10900123
the format how the date is stored is :

2004-02-04 11:14:00
0
 
LVL 9

Expert Comment

by:ankuratvb
ID: 10900169
>the format how the date is stored is :
>2004-02-04 11:14:00

IS this stored as a string(char array).
0
 
LVL 12

Accepted Solution

by:
stefan73 earned 40 total points
ID: 10900705
Hi santoshv25,
That's a bit strange.

Oracle can do a query like this:

    EXEC SQL SELECT (sysdate-
                     to_date('01-jan-1970','dd-mon-yyyy')) * (24*60*60)
               INTO :t
               FROM DUAL;

t will now be a time_t timestamp.

So before you dive into C, check if the problem can't be solved on the DBMS side.

Cheers,
Stefan
0
 
LVL 12

Expert Comment

by:stefan73
ID: 10900734
Try NVL instead:

update scr_ship_rec
set elapse_hours = NVL((nc_dt - pickup_dt) * 24 , NULL);
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial is posted by Aaron Wojnowski, administrator at SDKExpert.net.  To view more iPhone tutorials, visit www.sdkexpert.net. This is a very simple tutorial on finding the user's current location easily. In this tutorial, you will learn ho…
Windows programmers of the C/C++ variety, how many of you realise that since Window 9x Microsoft has been lying to you about what constitutes Unicode (http://en.wikipedia.org/wiki/Unicode)? They will have you believe that Unicode requires you to use…
The goal of this video is to provide viewers with basic examples to understand how to create, access, and change arrays in the C programming language.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now