Question calculating time difference

[neronikita]

Good afternoon,

I'm sure I am missing something obvious here, but I'm so frustrated with it I thought another pair of eyes might help. I am using Crystal 2008 and SQL Server 2008 express.

I have this formula:
DateDiff ("n",{DailyVehicleTracking.TimeStarted} ,{DailyVehicleTracking.TimeStopped} )
timestarted and timestopped are both Time(7) types. I just need to know how much time each job took. When I put in the formula and try to run it, it says a date is required. What am I doing wrong? Everything I found online says this should work.

Thanks in advance!

Di

 

[briangriffin]

What do the database fields look like when you drop them into the details section? For me it plugged in the date of 12/30/1899, and changing the database default options in CR didn't have any effect.

Regardless, you can probably beat CR into submission with a formula like this:

whileprintingrecords;
datetimevar v_begin := datetime(currentdate & " " & time({DailyVehicleTracking.TimeStarted}));
datetimevar v_end := datetime(currentdate & " " & time({DailyVehicleTracking.TimeStopped}));

datediff("n",v_begin,v_end)

 

[neronikita]

Thanks, I will try that... Had I realized what a pain this would be, I would have made it a datetime field in the first place. argh!

 

[neronikita]

I'm getting the error "Bad Time Format String" and it's highlighting my field names. The field is the time format... should I have used something else? I don't want to have to enter the date twice (I already have a date field).

Any ideas?

Thanks!

Di

 

[lbass]

Try:

whileprintingrecords;
datetimevar v_begin := datetime(currentdate, time({DailyVehicleTracking.TimeStarted}));
datetimevar v_end := datetime(currentdate,time({DailyVehicleTracking.TimeStopped}));
datediff("n",v_begin,v_end)

-LB

 

[neronikita]

Still getting the same error message.....

 

[lbass]

Sorry, that should have been:

whileprintingrecords;
datetimevar v_begin := datetime(currentdate, {DailyVehicleTracking.TimeStarted});
datetimevar v_end := datetime(currentdate,{DailyVehicleTracking.TimeStopped});
datediff("n",v_begin,v_end)

-LB

 

[neronikita]

Now it says a time is required here and it highlights my field. I don't understand - if it is formatted in Sql server as a time field, shouldn't it be reading it as a time field? Why isn't it?

-Di

 

[lbass]

If you right click on the field in Crystal->browse field, what datatype is identified?

-LB

 

[neronikita]

AH-HA! it says String, length 16. So what good does the Time(7) do in sql?

 

[lbass]

Next step is to show a sample of how this 16-character string displays. You need to convert it to a time.

-LB

 

[neronikita]

08:30:00.0000000

 

[lbass]

Convert your stringtimes to actual times by using formulas like this:

time(left({DailyVehicleTracking.TimeStarted},8))

Then use the formulas in the diff formula:

datediff("n",datetime(currentdate,{@TimeStarted}), datetime(currentdate,{@timeended}))

-LB

 

[neronikita]

You the Man! Thanks!!!!!!!! it worked!
A big fat gold star for you!

Thanks again,

Di