× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 21-Oct-2014 12:39 -0500, CRPence wrote:
On 21-Oct-2014 10:49 -0500, Gqcy wrote:
I have been looking for examples to convert the following string
into a valid timestamp:

"2014-07-25 08:28:04 AM"

I initially made a DS breaking apart date from time,
but I am now having trouble with the time string...

time_a = %char(%time(time_part:*usa):*ISO);

is not working...

Given the DS is as follows, plus both that the character data
matches that static alignment and all of the string values are valid
timestamp representations [despite being a composite of ISO and USA
formats]:

Dcl-ds TS_ISO_USAish ;
Date_Part char(10) ;
*n char(01) ;
Time_Part char(08) ;
*n char(01) ;
AMPM_Part char(02) ;
End-ds ;

And if embedding SQL is acceptable, then the following expression
should suffice:

set :time_a = timestamp(:Date_Part , time(:Time_Part)
+ case AMPM_Part when 'PM' then 12 else 0 end HOURS ) ;
/* TIME() scalar is for clarity, but should be optional */

Warning: The above is *incorrect*; and, the effectively identical algorithm appeared in two other replies to the original thread.


If any values might be suspect, or as generally safe coding practice
irrespective the confidence in the validity of the data, the RPG
should check the SQLSTATE and\or the SQLCODE after the SET statement
to ensure the expression was evaluated without error.


A necro-posting, sorry; just short of two years. But, to correct obvious flaws; seems to have been a mistake made thrice, as two others had coded effectively the same failing algorithm in that thread :-( Specifically, simply adding the +12Hours is *invalid* for having tested *only* for the presence of 'PM'. The given expression *incorrectly* adds twelve hours to what are `12:mm:ss PM` values, which results in `00.mm.ss` [effectively `12:mm:ss AM`], which is clearly wrong. Additionally for having tested *only* for the presence of 'PM', for those values that are `12:mm:ss AM`, 12 hours need to be subtracted to result in the expected `00.mm.ss`. The other time-values need not be adjusted from the `hh:mm:ss`, because those time-values remain the same across both *USA and *HMS formats; i.e. 01:00AM to 12:59PM appear as the same digits 01:00 to 12:59 but without the Meridian indicator.

I suppose had there been an eventual overflow condition reported for that time arithmetic/addition, for 12PM+12Hrs, then my suggesting about being sure to check the SQLSTATE results could have revealed that; but by tests, the SQL seems not to care. But what would have been a problem, is that assigning the given expression to the variable TIME_A, as a TIME type, would resulted in a sqlcode=-303 aka msg SQL0303 stating that the variable was not compatible with the result of the expression. Also, I forgot the colon in front of AMPM_Part variable used in the expression, thus failing to denote the reference as a Host Variable rather than a column [or an SQL variable, since whenever that is supported]. Clearly, that was something that I should have actually *tested* at the time [or at least more thoroughly than I did, if I did test], *before posting*; Oops.

So the corrected/function version of that SQL, but not nearly so pretty, follows; although just after, an additional example but much more succinct is offered, though availability is not assuredly available until IBM i 7.3:

Dcl-S time_a time ;
Dcl-ds TS_ISO_USAish ;
Date_Part char(10) ;
*n char(01) ;
Time_Part char(08) ;
*n char(01) ;
AMPM_Part char(02) ;
Time_HH char(02) overlay(Time_Part:1) ; // pbbly zoned better
End-ds ;

// using SQL
Exec SQL
set :time_a =
time( :Time_Part )
+ case :AMPM_Part
when 'PM' then case :Time_HH
when '12' then 0 else 12 end
when 'AM' then case :Time_HH
when '12' then -12 else 0 end
end HOURS
;

// Or using SQL with enhanced TIMESTAMP_FORMAT Meridian capability
Exec SQL
set :time_a =
TIME( TO_DATE( :TS_ISO_USAish
, 'YYYY-MM-DD HH:MI:SS AM' ) )
; -- requires enhancement for Meridian support; known to be on v7r3

// Or done in ILE RPG (RPGLE) instead of with SQLRPGLE
time_a = %Time( Time_Part : *HMS ) ;
// Check for AM/PM and make adjustments for 24hr time
Select ;
When AMPM_Part = 'PM'
and Time_HH<'12' ;
// and %SubDt(time_a:*HOURS)<12 ;
time_a += %Hours(12);
When AMPM_Part = 'AM'
and Time_HH='12' ;
// and %SubDt(time_a:*HOURS)=12 ;
time_a -= %Hours(12);
//else ; // time between 01:00AM and 12:59PM
// time_a needs no adjustments
EndSl ;



In case some test data is required/desired:

create table qtemp.gqcy
( ts_str char( 22 )
, ts_val timestamp
)
;
insert into qtemp.gqcy values
/* ts_str: pseudo_timestamp, ts_val: actual timestamp of ts_str */
('2014-07-25 00:00:00 AM' , '2014-07-25-00.00.00.000000' )
,('2014-07-25 12:00:00 AM' , '2014-07-25-00.00.00.000000' )
,('2014-07-25 12:59:59 AM' , '2014-07-25-00.59.59.000000' )
,('2014-07-25 01:00:00 AM' , '2014-07-25-01.00.00.000000' )
,('2014-07-25 01:01:01 AM' , '2014-07-25-01.01.01.000000' )
,('2014-07-25 04:04:04 AM' , '2014-07-25-04.04.04.000000' )
,('2014-07-25 10:10:10 AM' , '2014-07-25-10.10.10.000000' )
,('2014-07-25 11:11:11 AM' , '2014-07-25-11.11.11.000000' )
,('2014-07-25 12:00:00 PM' , '2014-07-25-12.00.00.000000' )
,('2014-07-25 12:12:12 PM' , '2014-07-25-12.12.12.000000' )
,('2014-07-25 12:59:59 PM' , '2014-07-25-12.59.59.000000' )
,('2014-07-25 01:00:00 PM' , '2014-07-25-13.00.00.000000' )
,('2014-07-25 01:13:13 PM' , '2014-07-25-13.13.13.000000' )
,('2014-07-25 04:16:16 PM' , '2014-07-25-16.16.16.000000' )
,('2014-07-25 10:22:22 PM' , '2014-07-25-22.22.22.000000' )
,('2014-07-25 11:23:23 PM' , '2014-07-25-23.23.23.000000' )

select * from qtemp.gqcy
where ts_val <> to_date( ts_str, 'YYYY-MM-DD HH:MI:SS AM' )
; -- above query returns zero rows, so dates are validated *same*

with cte as
( select q.*
, timestamp( date( left( ts_str, 10 ) )
, time( substr( ts_str, 12, 8 ) ) )
+ case when hour(substr( ts_str, 12, 8 ) ) = 12
then case right( ts_str, 2 )
when 'PM' then 0 else -12 end
when right( ts_str, 2 ) = 'PM'
then 12 else 0
end HOURS as ts_tst
from qtemp.gqcy as q
)
select *
from cte
where ts_val <> ts_tst
; -- above query returns zero rows, non-TO_DATE expression is valid

with cte as /* ....+....1....+....2.. */
( select q.* /* YYYY-MM-DD HH:MI:SS AM */
, timestamp( date( left( ts_str, 10 ) )
, time( substr( ts_str, 12, 8 ) ) )
+ case right( ts_str, 2 )
when 'PM' then case substr( ts_str, 12, 2 )
when '12' then 0 else 12 end
when 'AM' then case substr( ts_str, 12, 2 )
when '12' then -12 else 0 end
end HOURS as ts_tst
from qtemp.gqcy as q
)
select *
from cte
where ts_val <> ts_tst
; -- above query returns zero rows, non-TO_DATE expression is valid



As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.