extracting part of CURRENT TIMESTAMP 2

[Member 310024]

I would like to update a CHAR field with the digits (only) held within CURRENT TIMESTAMP.
However, being a person whose weakness is that they don't find it easy working with 'user friendly' objects, I haven't been able to successfully construct my UPDATE statement.

I thought it would be someting simple like this :-

UPDATE DB2TABLENAME
SET MYFIELD = SUBSTR(CURRENT TIMESTAMP,1,4)
WHERE KEYFIELD = 'XYZ';

But noooooooooo ... it's not that easy.
Can someone please put me out of my misery.

 

[Dagon]

Try substr(char(current timestamp), 1, 4) or year(current timestamp).

 

[Dagon]

Or you could even do "cast(current timestamp as char(4))".

 

[MarcLodge]

Kevin,
As mentioned over in a different forum, I think that if you run:
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,4)
FROM SYSIBM.SYSDUMMY1

You should get the result you're after. Note that I've chnaged the very last substr to give 4 chars, as you require CCYYMMDDHHSSmm, which I missed. This should give you back a 14 char field in the format you require. It does for me!

 

[MarcLodge]

Ignore that last one [flush]
What I meant was:
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)||
SUBSTR(CHAR(CURRENT TIMESTAMP),18,2)
FROM SYSIBM.SYSDUMMY1

Hope this solves it

Marc

 

[Member 310024]

Thank you for the responses, which I have recognized by allocating stars.
My problem is now solved.
My SQL now reads :-
UPDATE DB2TABLE1
SET
MYFIELD = SUBSTR(CHAR(CURRENT TIMESTAMP),1,4)||
SUBSTR(CHAR(CURRENT TIMESTAMP),6,2)||
SURSTR(CHAR(CURRENT TIMESTAMP),9,2)||
SURSTR(CHAR(CURRENT TIMESTAMP),12,2)||
SUBSTR(CHAR(CURRENT TIMESTAMP),15,2)||
SUBSTR(CHAR(CURRENT TIMESTAMP),18,2)
WHERE
INDENT_NO = 'XYZ';

You would be surprised how few people know how to do this.

 

[MarcLodge]

Pleased to have been of help. Many thanks for the star, much appreciated.
Marc