Converting a DateTime to a String

[BobSuruncle]

I've got a version 8.5 Crystal report that has to be upgraded to version 10.
We're connecting to DB2 using ODBC.
The 8.5 report used a QRY file as it's data source.
The contents are listed below:

SELECT
         PYMTS.*
        ,CLAIMS.*
FROM
 (SELECT 
	 T1.GRPOLNO
	,T1.GRSCTNO
	,T1.CERTNO
	,T1.STDCLMNO
	,T1.CLAIM_ID
	,T3.NAME
 FROM   
	 GGWDWHOP.TSCLM T1        
        ,GGWDWHOP.TGCPS T3                
 WHERE  
	T1.GRPOLNO  >= '{?From Contract Number}'                      
  AND   T1.GRPOLNO  <= '{?To Contract Number}'  
  AND 	T1.GRPOLNO   = T3.GROUP_NUMBER
  AND 	T1.GRSCTNO   = T3.SECTION
  AND   T1.TIMESTAMP =                              
  (SELECT MAX(T2.TIMESTAMP)                           
  FROM GGWDWHOP.TSCLM  T2                            
          WHERE T2.CLAIM_ID  = T1.CLAIM_ID              
            AND T2.TIMEDATE <=  {?End Date} ) ) CLAIMS    
JOIN
(SELECT
	T4.*
	FROM
   	 GGWDWHOP.TSPMT  T4
WHERE
        T4.ENTRYDT >= {?Start Date}    
 AND    T4.ENTRYDT <= {?End Date} 
) PYMTS
  ON CLAIMS.CLAIM_ID = PYMTS.CLAIM_ID

The field TIMESTAMP was returned as a String type field (basically because CR8.5 couldn't handle DateTimes in QRY files properly)
The format of the TIMESTAMP field was as follows:
yyyy-MM-dddd HH:mm:ss.milliseconds

The milliseconds are not really used, but the client has been seeing them for the last two years or so, and they think that they can't live without them.

In Crystal version 10, QRY files have been replaced by Command Files (i.e. a SQL statement built right into the report)
Using the existing SQL for the new report works, except for the fact that now the TIMESTAMP field is being returnd as a DateTime type. Because of this, there is no longer any millisecond element showing.

I tried tweaking the SQL to add a new field that would return the TIMESTAMP in a String format.

SELECT
         PYMTS.*
        ,CLAIMS.* 
        ,CHAR(CLAIMS.TIMESTAMP)  AS TSS
FROM
 (SELECT 
	 T1.GRPOLNO
	,T1.TIMESTAMP                                  
	,T1.GRSCTNO                                  
	,T1.CERTNO
	,T1.STDCLMNO
	,T1.CLAIM_ID
	,T3.NAME
 FROM   
	 GGWDWHOP.TSCLM T1        
        ,GGWDWHOP.TGCPS T3                
 WHERE  
	T1.GRPOLNO  >= '{?From Contract Number}'                      
  AND   T1.GRPOLNO  <= '{?To Contract Number}'  
  AND 	T1.GRPOLNO   = T3.GROUP_NUMBER
  AND 	T1.GRSCTNO   = T3.SECTION
  AND   T1.TIMESTAMP =                              
  (SELECT MAX(T2.TIMESTAMP)                           
  FROM GGWDWHOP.TSCLM  T2                            
          WHERE T2.CLAIM_ID  = T1.CLAIM_ID              
            AND T2.TIMEDATE <=  {?End Date} ) ) CLAIMS    
JOIN
(SELECT
	T4.*
	FROM
   	 GGWDWHOP.TSPMT T4
WHERE
        T4.ENTRYDT >= {?Start Date}    
 AND    T4.ENTRYDT <= {?End Date}
) PYMTS
  ON   CLAIMS.CLAIM_ID = PYMTS.CLAIM_ID

The statement runs, but the values are not correct.
For example the DateTime field returns
2002-05-17 01:51:08,
but the string version of this field returns
2002-10-09-00.15.03.182080

Sorry to be so long winded, but can anyone point me in the right direction?
I'm a Crystal guy, not a SQL guru.



Bob Suruncle

 

[blom0344]

The VARCHAR_FORMAT function will do what you want, but it will not show the milliseconds as such.
You can rebuild the entire string by using SUBSTR though if you really want the last bit.

Ties Blom

 

[BobSuruncle]

Thanks

Bob Suruncle