MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Can a timestamp be formatted by a concatenation?

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
pcmoonbeam
Beginner


Joined: 21 Nov 2005
Posts: 31
Topics: 10
Location: Orange County, California
PostPosted: Tue Feb 03, 2009 7:11 pm    Post subject: Can a timestamp be formatted by a concatenation? Reply with quote
I have a table to which I have added a new timestamp column (CREATE_DATE) . On a go forward basis, this column will be populated each time a row is added to the table with the current timestamp.

I have a column in the table called WRKPERIOD in DATE format. This column plus 2 months represents the date when the row was created, but I would like to somehow use this column (with the 2 months added in) and concatenate it with a fixed time in the format of HH:MM:SS.MMMMMM to create my new timestamp. The desired result would be a column that is in normal timestamp format.

Is there a way to do this without writing a COBOL program that would require two passes against the table?
Here's what my original SQL looks like minus the update of the CREATE_DATE timestamp column:
Code:
 UPDATE HOURS.A2BKCNT X
   SET X.FAMILY_ID =
   (SELECT Z.FAMILY_ID
                FROM FDBEL.REG_MASTER Z
                     WHERE X.SSNO = Z.PART_SSNO AND 0 = Z.RELATION_ID),
        LASTUPDT = CURRENT TIMESTAMP,
        USERID = 'JMN FAMID',
        CREATE_USERID = 'FBHR216'

_________________
Thanks,
PCMOONBEAM
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
PostPosted: Tue Feb 03, 2009 8:07 pm    Post subject: Reply with quote
pcmoonbeam,

Quite simple. It can be done in the same sql itself. (untested)
Code:

UPDATE HOURS.A2BKCNT X
   SET X.FAMILY_ID   = (SELECT Z.FAMILY_ID
                          FROM FDBEL.REG_MASTER Z
                         WHERE X.SSNO = Z.PART_SSNO
                           AND 0      = Z.RELATION_ID)
      ,LASTUPDT      = CURRENT TIMESTAMP
      ,USERID        = 'JMN FAMID'
      ,CREATE_USERID = 'FBHR216'
      ,CREATE_DATE   = TIMESTAMP(WRKPERIOD + 2 MONTHS,TIME('00:00:00'))



This will set the create_date = date(wkrperiod + 2 months)-00.00.00.000000

Alternatively you can use

Code:

      ,CREATE_DATE   = TIMESTAMP(WRKPERIOD + 2 MONTHS,CURRENT TIME)



This will set the create_date = date(wkrperiod + 2 months)-18.00.52.000000
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pcmoonbeam
Beginner


Joined: 21 Nov 2005
Posts: 31
Topics: 10
Location: Orange County, California
PostPosted: Wed Feb 04, 2009 11:31 am    Post subject: Reply with quote
Kolusu,
I'll give it a try and let you know the result. Morning Cofee
_________________
Thanks,
PCMOONBEAM
Back to top
View user's profile Send private message
pcmoonbeam
Beginner


Joined: 21 Nov 2005
Posts: 31
Topics: 10
Location: Orange County, California
PostPosted: Wed Feb 04, 2009 11:37 am    Post subject: Reply with quote
Kolusu,
You are my hero!! It worked like a charm. Thank you very much. claps
_________________
Thanks,
PCMOONBEAM
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group