ca.portal.admin

Timestamp in SQL

Discussion created by ca.portal.admin on Apr 22, 2010
Latest reply on Apr 22, 2010 by ca.portal.admin
I would like to display the fields CTIME and UTIME (both PIC X(64) USAGE
BIT) in the USERDATA record using SQL.

=20

The SQL Reference seems to imply that the TIMESTAMP function might do it, b=
ut it's not working for me.

=20

Does anyone have any suggestions on how to get these fields displayed?

=20

=20

IDMSBCF 16.0 CA-IDMS Batch Command
Facility

=20


=20


SET SESSION CURRENT SCHEMA IDMSSECU;


Status =3D 0 SQLSTATE =3D 00000


SET TRANSACTION READ ONLY;


Status =3D 0 SQLSTATE =3D 00000


SELECT USERID, CUSER, TIMESTAMP(CTIME), UUSER, TIMESTAMP(UTIME)


FROM USERGROUP


WHERE GROUPID =3D 'TRFSQL_GROUP'


ORDER BY USERID;


Status =3D -4 SQLSTATE =3D 42604 Messages follow:


DB005000 C0M324: Message for module IDMSBCF, SQL statement number : 1.


DB005528 C-4M324: Parameter 1 of function TIMESTAMP, data type or value in =
error.

=20


AutoCommit will COMMIT transaction


=20


Command Facility ended with errors
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Timestamp in SQL
"Try the following - let us know how you go - cheers - Gary

SELECT cast ( USERid as char(8) ) as userid ,
cast ( CUSER as char(8) ) as createby ,
cast ( uUSER as char(8) ) as updateby ,
CAST ( CTIME as timestamp) as created ,
CAST ( uTIME as timestamp) as updated
FROM idmssecu3.uSERGROUP
where userid like 'A%'
ORDER BY USERID;
*+
*+ USERID    CREATEBY  UPDATEBY  CREATED
*+ ------    --------  --------  -------
*+ ACCAST    JDASAM    JDASAM    1996-06-17-10.06.19.729325
*+ ADXTRE    JISDAM    JISDAM    2007-05-10-09.46.02.586688
*+ AHXBAR    JISDAM    JISDAM    2003-12-05-10.04.05.730798
*+ AJDXT2    JISDAM    JISDAM    2003-09-30-15.05.43.881442
*+ AJDXT3    JISDAM    JISDAM    2003-09-30-15.05.32.890680
*+ AJXTUR    JISDAM    JISDAM    2003-12-05-10.26.22.766884
*+ AKLPYP    JISDAM    JISDAM    2003-12-05-10.22.52.375983
*+ ALSAMP    JISDAM    JISDAM    1999-10-21-08.07.00.991972
*+ ALXBUL    JISDAM    JISDAM    2004-11-12-09.14.14.849564
*+ ALXWIL    JISDAM    JISDAM    2003-12-05-10.28.25.902964
*+ AWJABR    JISDAM    JISDAM    2003-12-05-10.03.37.134226
*+
*+ UPDATED
*+ -------
*+ 1996-06-17-10.06.19.729325
*+ 2007-05-10-09.46.02.586688
*+ 2003-12-05-10.04.05.730798
*+ 2003-09-30-15.05.43.881442
*+ 2003-09-30-15.05.32.890680
*+ 2003-12-05-10.26.22.766884
*+ 2003-12-05-10.22.52.375983
*+ 1999-10-21-08.07.00.991972
*+ 2004-11-12-09.14.14.849564
*+ 2003-12-05-10.28.25.902964
*+ 2003-12-05-10.03.37.134226
*+
*+ 11 rows processed


Gary Cherlet
Justice Technology Services
Department of Justice, SA Government
Telephone +61 (0)8 8226 5199
Facsimile +61 (0)8 8226 5311
Mobile +61 (0)41 333 1613
MailTo:gary.cherlet@sa.gov.au

This e-mail message and any attachments are qualified as follows: Addressing: If you have received this e-mail in error, please advise by reply e-mail to the sender. Please also destroy the original transmission and its contents. Confidentiality: This e-mail may contain confidential information which also may be legally privileged. Only the intended recipient(s) may access, use, distribute or copy this e-mail. Individual Views: Unless otherwise indicated, the views expressed are those of the sender, not Justice Technology Services. Computer Viruses: It is the recipient's responsibility to check the e-mail and any attached files for viruses.

Outcomes