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
midrange.com
