0

Quick question. I've been googling but have yet to find an answer to this problem. I am creating a sub report inside CF Report Builder. The problem I am having is I have to pass a date into my query in the sub report, and the date is coming in as an invalid format.

My query for the main report gives a month end as a date, here's the code that creates the date:

TO_DATE('09/30/2018','MM/DD/YYYY')

But when that gets passed to the sub report/sub query, it comes in as this:

{ts '2018-09-30 00:00:00'}

... even though the actual query just returns '09/30/2018'. I know its just the way the report builder is formatting the date, but how can I get it to pass in as 9/30/2018 so it won't error out in my subquery?

SOS
  • 6,430
  • 2
  • 11
  • 29
user2921015
  • 69
  • 1
  • 9
  • 4
    The `{ts ... }` formatting that you see is how ColdFusion represents a date/time object. So it looks like you need to format that date/time object into a string before passing to your sub report/sub query. See [DateFormat()](https://cfdocs.org/dateformat) or [DateTimeFormat()](https://cfdocs.org/datetimeformat) depending on the actual data type it is. – Miguel-F Nov 01 '18 at 20:17
  • It sounds like the Builder is passing a date object, which should be fine for a query, so I wonder if the subquery SQL is wrong. Could you post the subquery and the error? – SOS Nov 01 '18 at 23:00
  • " The {ts ... } formatting that you see is how ColdFusion represents a date/time object. So it looks like you need to format that date/time object into a string before passing to your sub report/sub query. See DateFormat() or DateTimeFormat() depending on the actual data type it is." Forcing it to be a string worked. Thanks! – user2921015 Nov 02 '18 at 14:47
  • Just a note, the query doesn't actually return `09/30/2018`. That's just the way it ultimately displays it to you. The query probably actually returns something similar to `120,118,9,30,1,1,1`. So you'll have to format it in whatever way you need to consume it. Some representations are coercible to a different format without any intervention, but it depends. Gotta love how SQL engines store dates. :-/ – Shawn Nov 02 '18 at 20:40
  • @user2921015 - Haven't used Report Builder, so maybe it's queries are different, but ... normally it's better *not* to convert dates into strings for queries. Instead you just pass in the date object. – SOS Nov 02 '18 at 23:37

0 Answers0