Dates and Times in sqlReports

One of the biggest problems people run into with sqlReports is their query works fine in something like Oracle SQL Developer, but won’t work in sqlReports.  The most common issue is date related.  With sqlReports you may need to use to_date to specify date types in where clauses, and either to_date or to_char in select clauses to format the date.  For example, where entrydate > 9/1/2013 may work in a sql client, but in sqlReports, you may need to use something like where entrydate > to_date(‘9/1/2013’,’MM/DD/YYYY’).  Also, the formatting needs to be the same for both the field name and the criteria.  If you format the field as MM/DD/YYYY, then also format the criteria as MM/DD/YYYY.

If your query is using the between command to search between two dates, you may need to do something like the following from the Login History report:

where to_date(to_char(acchist.loginattemptdate,'MM/DD/YYYY'),'MM/DD/YYYY') between to_date('%param1%','MM/DD/YYYY') and to_date('%param2%','MM/DD/YYYY')

The above is making sure everything is being seen in date format, but with some fields you have to use to_char first to get them into a format you can work with for to_date.

Formatting Dates in Select

If you wish you to format a date field a specific way in the results, you’ll want to format the field within the select clause.  For example, if you just have dob in the select, you’ll get a date and timestamp in the results like so, 1996-12-13 00:00:00.0.  To return that as 12/13/1996 instead, in the select change dob to to_char(dob,'MM/DD/YYYY').

Formatting Time in Select

If you’re trying to pull information from a field that has time and you just use the field name in the select, you’ll get a number that could be in the thousands on the results. The reason why is time is actually stored as the number of seconds after midnight.  To convert the seconds into a readable format, use something like the following, which is from the Daily Attendance – Students report:

regexp_replace(to_char(to_date(attt.time_in,'SSSSS'),'HH.MI AM'),'\.',chr(58))

What the above does is first convert a field called time_in into a normal time expression.  The time is put into a format of HH.MI AM and then the period is replaced with chr(58), which is the ascii code for a colon.  If you try to put HH:MI AM in the to_char part the report won’t return anything because you can’t use colon’s or semi-colon’s in sqlReports because it confuses the tlist_sql backbone.  You can reference those two characters using chr coding instead (the chr command for a semi-colon is chr(59)), so the regexp_replace part replaces the period in HH.MI with a colon.

Another method was posted by Adam Larsen on PSUG and includes using the chr(58) command where the colon would appear with concatenation rather than doing a regexp_replace.  Using the example above, the following will also show the time correctly and is easier to code:

to_char(to_date(attt.time_in,'SSSSS'),'HH'||chr(58)||'MI AM')


  Back to the List of Articles