Error While Trying to cast as Timestamp

Database
Enthusiast

Error While Trying to cast as Timestamp

Hi,

I'm trying to modify a code script and trying to add a few new fields to it. The tricky part I come across while try to add these fields is while trying to cast them. I would need the format of a timestamp (calculated based on division / modulo of two fields) [Format is hh:mm:ss], but unable to cast it using timestamp.

Average Speed

=If IsNull([Tot_Answered]) Then "" Else FormatNumber(Floor(([Delay_Dur]/[Tot_Answered])/3600);"00")+ ":"+FormatNumber(Floor(Mod(([Delay_Dur]/[Tot_Answered]) ;3600)/60) ;"00")+ ":" + FormatNumber(Mod(Mod(([Delay_Dur]/[Total_Answered]);3600);60);"00")

The code script I use is :

SELECT  
Table4.Data_Dt, Table3.Service_Name,
SUM(((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 2 AND ( Table2.Handle_Type ) IN ('Abandon') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))) AS "Tot_Offered",
SUM ((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END ))) AS "Tot_Answered",
SUM (( CASE WHEN ( DRV_Table1.Resource_Type ) = 2 AND ( Table2.Handle_Type ) IN ('Abandon') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )) AS "Tot_Abandoned",
SUM (( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ( 'Handle') THEN DRV_Table1.Q_Dur ELSE 0 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ( 'Handle') THEN DRV_Table1.Dlay_Dur ELSE 0 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Rng_Dur ELSE 0 END )) AS "Delay_Dur",
SUM (( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Tlk_Dur*1.0000 ELSE 0*1.0000 END )) AS "Tot_Tlk_Tm",
SUM (( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Wrk_Dur ELSE 0 END )) AS "Tot_Wrk_Tm",
CASE WHEN CAST((SUM((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )))) AS DECIMAL (10,2) ) IS NULL
OR CAST((SUM((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )))) AS DECIMAL (10,2) ) = 0 THEN 0
ELSE
(1.0000 * CAST((SUM((CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) = 'Handle' AND ( Table2.Handle_Type_Code2 ) = 'In Service' THEN 1 ELSE 0 END ))) AS DECIMAL (10,2)) /
CAST((SUM((( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Short') THEN ( DRV_Table1.Seg_Count ) ELSE 0 END )))) AS DECIMAL (10,2) ))*100 END AS "Srv_Lvl(%)",
SUM(( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Hld_Dur ELSE 0 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Tlk_Dur*1.0000 ELSE 0*1.0000 END )
+( CASE WHEN ( DRV_Table1.Resource_Type ) = 3 AND ( Table2.Handle_Type ) IN ('Handle') THEN DRV_Table1.Wrk_Dur ELSE 0 END )) AS "Hndl_Tm"
FROM Table4
INNER JOIN (
SELECT * FROM Table1 WHERE Strt_Dt_ID BETWEEN (CAST ('?START_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100
AND (CAST ('?END_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100 ) DRV_Table1
ON (Table4.Date_ID=DRV_Table1.Strt_Dt_ID)
INNER JOIN Table2 ON (DRV_Table1.Seg_Result_ID=Table2.Seg_Result_ID)
INNER JOIN Table3 ON (DRV_Table1.Resource_ID=Table3.Resource_ID)

WHERE (
Table3.Service_ID IN ( 9572511 ) AND Table3.Skl_Grp_ID IN ( 258, 0 ) AND
Table4.Date_ID BETWEEN (CAST ('?START_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100 AND
(CAST ('?END_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100 )
GROUP BY 1, 2
ORDER BY 1

I believe, this could also be done by casting it as varchar. Can some help me modify this code to add the new fields ?

Tags (1)
5 REPLIES
Junior Contributor

Re: Error While Trying to cast as Timestamp

Hi Sarang,

the field seems to be a number of seconds, of course can't you cast that to a Time.

You need an Interval instead:

Tot_Answered * interval '00:00:01' second
Enthusiast

Re: Error While Trying to cast as Timestamp

Hi Dieter,

I tried modifying the script to add the new fields, though I come across errors in this particular line in the above code :

SELECT * FROM Table1 WHERE Strt_Dt_ID  BETWEEN (CAST ('?START_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100  
AND (CAST ('?END_DT' AS DATE FORMAT 'YYYY-MM-DD')) - DATE '1900-01-01' + 100 ) DRV_Table1
ON (Table4.Date_ID=DRV_Table1.Strt_Dt_ID)

The data types of the Strt_Dt_Id field of Table1 and Date_ID of Table4 (above) is integer, but casting a date input by the user at runtime gives out an error.


What would be correct usage ?

Junior Contributor

Re: Error While Trying to cast as Timestamp

Hi Sarang,

there are many ways to store a date in an integer :)

So how is the date stored (yyyymmdd?) and what's the user's input?

Enthusiast

Re: Error While Trying to cast as Timestamp

Hi Dieter,

The date's stored as a 5 digit integer (just like how Excel stores dates in General format). I would want to extract data for a particular date range. I'm trying to cast the Start and End dates that the user inputs at runtime and fetch the records pertaining to that date range from Table1.

Junior Contributor

Re: Error While Trying to cast as Timestamp

Hi Sarang,

Excel stores the number of days since a starting date, 1900-01-01 is day 1. So the difference between the start_dt and this starting date is almost correct, you just need to substract '1899-12-30' instead of '1900-01-01' ('1899-12-30' instead of 31 because Excel's calculation includes the date '1900-02-29' which never existed).

But why do you substract 100 from this?

And of course the end user has to key in a correct date with the correct format, yyyy-mm-dd.