When running the below CARLa I need the date portion to format LAST_DATETIME to be in ISO format YYYY-MM-DD, as is the date is formatted dDMMMYYYY (where "d" is zero supressed for single char days).  I've tried using the $DATE and USDATE but currently can not get the format we need, short of runnning it thru seperate processing steps using CARLa or SORT, etc... 

Does anyone have any advice or suggestions?

newlist type=access nodetailinherit required  ll=500 retain
 define tot_count("Occurrence",10,udec$abbr) sum(access_count_big)
 define avg_reclen avg(record_length)
 define first_tod_sum(,"First occurrence") min(last_datetime)
 define last_tod_sum(,"Last occurrence") max(last_datetime)
 define poesep(1,":",hdr$blank) boolean    where exists(utoken_poeclass)
select sim_via=(ID_USER, ID_GROUP), REQ_STATUS_ACCESS=NO,
       class=DATASET    and access_profile<>" "
  sortlist complex(3),
           access_profile(44),
           resource(44),
           LAST_DATETIME(21),
           userid(8),
           userid:name(20),
           sim_via,
           sim_via_groups,
           intent(8),
           ACCESS_ALLOWED(8),
           ACCESS_COUNT,
           ACCESS_GLOBAL,
           ACCESS_OPERATIONS | ";" |,
           access_result(5,"AccRC",dec)

Regards,

LAST_DATETIME is a DATETIME field that contains the date and time.  The formats available are listed in the manual as GROUP 2.  You must convert this field into a DATE ONLY field to apply any of the GROUP 1 formats, such as $DATE.  This is achieved with (if memory serves):

define last_date($date,10) as convert(last_datetime,internaldatetime,date)

When running the below CARLa I need the date portion to format LAST_DATETIME to be in ISO format YYYY-MM-DD, as is the date is formatted dDMMMYYYY (where "d" is zero supressed for single char days).  I've tried using the $DATE and USDATE but currently can not get the format we need, short of runnning it thru seperate processing steps using CARLa or SORT, etc... 

Does anyone have any advice or suggestions?

newlist type=access nodetailinherit required  ll=500 retain
 define tot_count("Occurrence",10,udec$abbr) sum(access_count_big)
 define avg_reclen avg(record_length)
 define first_tod_sum(,"First occurrence") min(last_datetime)
 define last_tod_sum(,"Last occurrence") max(last_datetime)
 define poesep(1,":",hdr$blank) boolean    where exists(utoken_poeclass)
select sim_via=(ID_USER, ID_GROUP), REQ_STATUS_ACCESS=NO,
       class=DATASET    and access_profile<>" "
  sortlist complex(3),
           access_profile(44),
           resource(44),
           LAST_DATETIME(21),
           userid(8),
           userid:name(20),
           sim_via,
           sim_via_groups,
           intent(8),
           ACCESS_ALLOWED(8),
           ACCESS_COUNT,
           ACCESS_GLOBAL,
           ACCESS_OPERATIONS | ";" |,
           access_result(5,"AccRC",dec)

Regards,

Rob, that worked like a charm for the ISO date.   My bad, I didn't specify the time in my original post, but how can I display the time now without the previous date format?  I'd like to have HH:MM:SS.mmm format which is what the time was already showing up in when I put a length of 21 on it.

Thank you,

LAST_DATETIME is a DATETIME field that contains the date and time.  The formats available are listed in the manual as GROUP 2.  You must convert this field into a DATE ONLY field to apply any of the GROUP 1 formats, such as $DATE.  This is achieved with (if memory serves):

define last_date($date,10) as convert(last_datetime,internaldatetime,date)

When running the below CARLa I need the date portion to format LAST_DATETIME to be in ISO format YYYY-MM-DD, as is the date is formatted dDMMMYYYY (where "d" is zero supressed for single char days).  I've tried using the $DATE and USDATE but currently can not get the format we need, short of runnning it thru seperate processing steps using CARLa or SORT, etc... 

Does anyone have any advice or suggestions?

newlist type=access nodetailinherit required  ll=500 retain
 define tot_count("Occurrence",10,udec$abbr) sum(access_count_big)
 define avg_reclen avg(record_length)
 define first_tod_sum(,"First occurrence") min(last_datetime)
 define last_tod_sum(,"Last occurrence") max(last_datetime)
 define poesep(1,":",hdr$blank) boolean    where exists(utoken_poeclass)
select sim_via=(ID_USER, ID_GROUP), REQ_STATUS_ACCESS=NO,
       class=DATASET    and access_profile<>" "
  sortlist complex(3),
           access_profile(44),
           resource(44),
           LAST_DATETIME(21),
           userid(8),
           userid:name(20),
           sim_via,
           sim_via_groups,
           intent(8),
           ACCESS_ALLOWED(8),
           ACCESS_COUNT,
           ACCESS_GLOBAL,
           ACCESS_OPERATIONS | ";" |,
           access_result(5,"AccRC",dec)

Regards,

You can use the CONVERT function from the DEFINE command to split off the time part of a datetime field.  This results in an SMFTIME value.  See here for format names.  So

define last_time(smftime,8) as convert(last_datetime,internaldatetime,smftime)

Rob, that worked like a charm for the ISO date.   My bad, I didn't specify the time in my original post, but how can I display the time now without the previous date format?  I'd like to have HH:MM:SS.mmm format which is what the time was already showing up in when I put a length of 21 on it.

Thank you,

LAST_DATETIME is a DATETIME field that contains the date and time.  The formats available are listed in the manual as GROUP 2.  You must convert this field into a DATE ONLY field to apply any of the GROUP 1 formats, such as $DATE.  This is achieved with (if memory serves):

define last_date($date,10) as convert(last_datetime,internaldatetime,date)

When running the below CARLa I need the date portion to format LAST_DATETIME to be in ISO format YYYY-MM-DD, as is the date is formatted dDMMMYYYY (where "d" is zero supressed for single char days).  I've tried using the $DATE and USDATE but currently can not get the format we need, short of runnning it thru seperate processing steps using CARLa or SORT, etc... 

Does anyone have any advice or suggestions?

newlist type=access nodetailinherit required  ll=500 retain
 define tot_count("Occurrence",10,udec$abbr) sum(access_count_big)
 define avg_reclen avg(record_length)
 define first_tod_sum(,"First occurrence") min(last_datetime)
 define last_tod_sum(,"Last occurrence") max(last_datetime)
 define poesep(1,":",hdr$blank) boolean    where exists(utoken_poeclass)
select sim_via=(ID_USER, ID_GROUP), REQ_STATUS_ACCESS=NO,
       class=DATASET    and access_profile<>" "
  sortlist complex(3),
           access_profile(44),
           resource(44),
           LAST_DATETIME(21),
           userid(8),
           userid:name(20),
           sim_via,
           sim_via_groups,
           intent(8),
           ACCESS_ALLOWED(8),
           ACCESS_COUNT,
           ACCESS_GLOBAL,
           ACCESS_OPERATIONS | ";" |,
           access_result(5,"AccRC",dec)

Regards,