yyyymmddhhmmss from SQL only (not COBOL prog) 1

[Member 310024]

Using SQL only (not COBOL ws fields) I would like to to put the current date and time into a char(14) field, in the format yyyymmddhhmmss.
In COBOL this would be very straight forward, but I am writing a data-fix jcl/sql which needs to update a record, and then needs to store a separate history/audit record containing the current date+time (in the format yyyymmddhhmmss) and a reference to the updated record.

I have searched ref manuals and the internet and asked lots of people at work, and experimented with all sorts of syntax possibilities, but cannot get a solution that works on our IBM mainframe MVS DB2 system.

I know this is not a general COBOL question but I cannot find the SQL forum, and I've noticed that many of the questions in this forum are not strictly COBOL questions, but rather DB2, JCL etc.

 

[MarcLodge]

There is a IBMB2 forum that you might like to try in future, but in answer to your question I would try something similar to:

SELECT SUBSTR(CURRENT TIMSTAMP,1,4)||SUBSTR(CURRENT TIMESTAMP,6,2)||SUBSTR(CURRENT TIMESTAMP,9,2)||SUBSTR(CURRENT TIMESTAMP,11,2)||SUBSTR(CURRENT TIMESTAMP,14,2)

I haven't tested this out, as I'm not on a MF at the moment so the syntax may not be 100%, but I think it should give you what you are after.

HTH
Marc

 

[Member 310024]

As an experiment, I'm trying to put the first 4 chars of
CURRENT TIMESTAMP into a CHAR(30) field called PMT_COMMENT.
The CURRRENT TIMESTAMP holds 2003-07-10-19.04.40.688472 as an example. So in effect, I want to update PMT_COMMENT with the year ie 2003.

This is what happens :-
UPDATE SDSSV3A7.MSF35401
SET
PMT_COMMENT = SUBSTR(CURRENT TIMESTAMP,1,4)
WHERE
IDENT_NO = '6175001R';

This doesn't work, sqlcode = -171 which means
INVALID DATA TYPE, LENGTH OR VALUE FOR ARGUMENT.

but PMT_COMMENT = CURRENT TIMESTAMP does work
but it's not in the format I want.

 

[MarcLodge]

It's because DB2 holds dates etc. internally in a funny format. You could do this with SELECT YEAR(CURRENT TIMESTAMP) etc. but that is still going to give you a format where you cannot use the || concatenation character. What I would suggest using is:

SELECT SUBSTR(CHAR(CURRENT TIMESTAMP),1,4)||
SUBSTR(CHAR(CURRENT TIMESTAMP),6,2)||
SUBSTR(CHAR(CURRENT TIMESTAMP),9,2)||
SUBSTR(CHAR(CURRENT TIMESTAMP),12,2)||
SUBSTR(CHAR(CURRENT TIMESTAMP),15,2)
FROM SYSIBM.SYSDUMMY1

which is a bit cumbersome, but should work.

Marc

 

[Member 310024]

Marc

Thanks for trying ... but that doesn't work.
I have tried just about every combination and permutation of SUBSTR & CHAR & SELECT & CURRENT TIMESTAMP but nothing has yet worked.

I am amazed that someting like this isn't used reasonably frequently. When I find examples of the UPDATE statement in texts and internet searches, they always have the most simplistic examples ... that are so simplistic, they may as well not have them.

I've put a posting on IBMB2 forum ... maybe I'll get something back from there.

Regards,
Kevin from Canberra, Australia

 

[MarcLodge]

Kevin,
I'll follow this up in the db2 forum, but the bit of SQL I posted last works perfectly for me. It returns a 14 char field in the format you specified......