Friday, March 29, 2019

Crystal Report Tricks: Using special fields in formulas

When you want to use a special field in Crystal Reports in a formula, you can specify the special field  by using the special field name without spaces.  I admit I haven't tried them all, but in this case, the comments are multi-line and I wanted to grab just the first line.

split(ReportComments,chr(13))[1]




Friday, March 22, 2019

Oracle Tricks: Getting a range of numbers

Another method for getting a range of dates from Oracle PL/SQL

select last_day(to_date(to_char(months.month) || '/1/' ||
                        to_char(years.year),
                        'mm/dd/yyyy')) as range_EOM
  from (select rownum year from dual connect by level <= 2019) years,
       (select rownum month from dual connect by level <= 12) months
 where years.year >= 2000
   and months.month >= 1

Friday, March 1, 2019

Grabbing UTC Hour from Oracle

A simple sql script for obtaining gmt hour from Oracle
select sys_extract_utc(systimestamp) as UTC,
extract(hour from sys_extract_utc(systimestamp) ) as UTC_HOUR,
extract(hour from cast(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as timestamp) ) as  local_hour
from dual