how to change the datetime format into numeric format in crystal reports
-
Hi friends, i need big help.. my programme generates a crystal reports by following view... for a given batch need to calculated the actual running time = ((starttime - endtime) - sum(temporally_stoptime - restarttime)) MY View is as follows.....
CREATE VIEW titles_view AS select A.transID,A.Machine,Qlityno,batchnno,b.BTDes,color,startime,endtime,Cast((datediff(n,startime,endtime)- (SELECT ISNULL(SUM(DATEDIFF( n , TStoptime , Restarttime )),0) FROM FINTransTempstops B WHERE B.tranid = A.transid and B.TStoptime is not null))as integer) as cf_TimeTook,exp_weight,actual_meters,specspeed,isrefish,next_process from finbatchinfo A left join FINTransTempstops c on A.transID = c.TranID left join FINBatchType B on A.BTID = B.BTID where endtime is not null --and startime >=convert(datetime,'2006-08-21',121) and --startime <= convert(datetime,'2006-08-31',121) group by A.transID,A.Machine,Qlityno,batchnno,b.BTDes,color,startime,endtime,exp_weight,actual_meters,specspeed,isrefish,next_process --order by A.machine GOthis calculation is done in the view..... i take the actual time in minutes.. and then cast in to integer ....view gives the correct information but when i generate the report it shows the value in datetime format .... but i need to print the numeric value in the report(even in minute format also fine).. I need to show the actual running time in minutes.. when i check the format of the field in crystal report it is numeric...but at run time it gives the value in datetime format.....:confused::confused: all the dates in above in Database is datetime datatype.... Pls how can solve this problem..... Pls help meeee......:((:((:(( thanks...anusha wickramaratne
-
Hi friends, i need big help.. my programme generates a crystal reports by following view... for a given batch need to calculated the actual running time = ((starttime - endtime) - sum(temporally_stoptime - restarttime)) MY View is as follows.....
CREATE VIEW titles_view AS select A.transID,A.Machine,Qlityno,batchnno,b.BTDes,color,startime,endtime,Cast((datediff(n,startime,endtime)- (SELECT ISNULL(SUM(DATEDIFF( n , TStoptime , Restarttime )),0) FROM FINTransTempstops B WHERE B.tranid = A.transid and B.TStoptime is not null))as integer) as cf_TimeTook,exp_weight,actual_meters,specspeed,isrefish,next_process from finbatchinfo A left join FINTransTempstops c on A.transID = c.TranID left join FINBatchType B on A.BTID = B.BTID where endtime is not null --and startime >=convert(datetime,'2006-08-21',121) and --startime <= convert(datetime,'2006-08-31',121) group by A.transID,A.Machine,Qlityno,batchnno,b.BTDes,color,startime,endtime,exp_weight,actual_meters,specspeed,isrefish,next_process --order by A.machine GOthis calculation is done in the view..... i take the actual time in minutes.. and then cast in to integer ....view gives the correct information but when i generate the report it shows the value in datetime format .... but i need to print the numeric value in the report(even in minute format also fine).. I need to show the actual running time in minutes.. when i check the format of the field in crystal report it is numeric...but at run time it gives the value in datetime format.....:confused::confused: all the dates in above in Database is datetime datatype.... Pls how can solve this problem..... Pls help meeee......:((:((:(( thanks...anusha wickramaratne
Hi Anusha, You have to do casting one more time for minute on the following line: Cast((datediff(n,startime,endtime)- (SELECT ISNULL(SUM(DATEDIFF( n , TStoptime , Restarttime )),0) You need to cast it again to convert this resultset in the Minute format. Let me know in case you are not able to do that.
Regards, Amit Kumar HDISM, PGDCA, MCP, MCAD, MCSD amit_chikara2002@hotmail.com