How to split into correct date & time format

[arifg2005]

Hello all,

I have a table which has a field called DATCOL 16A.The value contains in the field like 02/05/2012 07:04...Now I have to split this value as date and time, and write into another table..

for ex:

Table1

DATCOL(02/05/2012 07:04)


Table2

DAT_COL -- 02/05/2012 (Expected Output)
TIM_COL -- 07:04 (Expected Output)



Note:

DATCOL 16A
DAT_COL L (Date format)
DAT_COL T (Time format)


Kindly help me out,
Thanks and regards,
Syed Arif

[as400Pro]

Re: How to split into correct date & time format

d found           s              3  0                                     
d Mydate          s             10                                        
d Mytime          s              5                                        
d text            s             16    inz('02/05/2012 07:04')             
                                                                          
 /free                                                                    
                                                                          
              found = %scan(' ':text);                                    
              Mydate = %subst(text:1:found-1);                            
              Mytime = %subst(text:found+1);                              
                                                                          
              *Inlr = *On;                                                
 /end-free 

[B.Hauser]

Re: How to split into correct date & time format

What do you need? Real Date and Time values or do you only want to split the string into 2 parts?

If you need a timestamp value and are on release 6.1 or higher, you can easily use the SQL scalar function TIMESTAMP format to convert the string into a real time stamp. With the %Date and %Time built-in-functions (or the appropriate SQL functions you can split the timestamp into a date and time field or you can overlay the timestamp with a date and time field.

D MyDS         DS                        qualified
D  Timestamp                         Z  
D   Date                             D  Overlay(Timestamp) DatFmt(*ISO)
D   Time                             T  Overlay(Timestamp: *Next) TimFmt(*ISO)

D  MyChar      S                   16A  Inz('02/05/2012 07:04')
 /Free
   Exec SQL  
     Set :MyDS.Timestamp = Timestamp_Format(:MyChar, 'MM/DD/YYYY HH24:MI');

Birgitta

[arifg2005]

Re: How to split into correct date & time format

d found           s              3  0                                     
d Mydate          s             10                                        
d Mytime          s              5                                        
d text            s             16    inz('02/05/2012 07:04')             
                                                                          
 /free                                                                    
                                                                          
              found = %scan(' ':text);                                    
              Mydate = %subst(text:1:found-1);                            
              Mytime = %subst(text:found+1);                              
                                                                          
              *Inlr = *On;                                                
 /end-free 

Hello as400pro,

Thanks for your reply, Is it possible to assign a character variable to date or time field???

It will throw Date or time stamp error..

Thank you

[as400Pro]

Re: How to split into correct date & time format

I can't follow your question: its like a winding path through the Forbidden Forest!

It is in a character field --- Took it from a character field to two other character fields.

Do you have something against Characters?