Skip to main content
S
Stevievegas49
Participant
December 12, 2012
Question

datetime parameter in coldfusion report builder

  • December 12, 2012
  • 1 reply
  • 2389 views

Hi,

I'm trying to create a report in coldfusion report builder that takes 2 parameters; startdate and enddate. These two parameters correspond to two fields in my sql database. However, both the fields are of type datetime and I can't work out how to insert them into my report? If I change the type in the report to string I get an error message so it's not that. Had a look on the internet but haven't been able to find any answers.

I'm using coldfusion mx7

Does anybody have an idea?

Thanks

Stevie

This topic has been closed for replies.

1 reply

J
jlig
Inspiring
January 10, 2013

Stevie, In the WHERE statement of your ColdFusion Report Builder SQL, add DATE_FORMAT to the fields like this:

(Note that I'm only referencing a single database field called srCompDate)

---------------------------------

WHERE DATE_FORMAT(tblSalesReport_history.srCompDate, '%m/%d/%Y') BETWEEN '#param.startDate#' AND '#param.endDate#'

---------------------------------

Now you can use these parameters on your report like this:

---------------------------------

"(" & param.startDate & " thru " & param.endDate & ")"

---------------------------------

C
Cozmo2
Inspiring
February 8, 2013

I'm using Cold Fusion 9 and I'm having a similar problem. I have a report that contains a subreport. On the subreport I only want records that have a grad_date (type=datetime) greater than the date_rec (type=datetime).  I have defined a param in the subreport with a data type of Time Stamp.

I get the following error when I try to save the query:

The Report Builder was unable to retrieve column information and cannot add in the query fields to the report automatically.

This is the error returned by the server:[Macromedia].....Conversion failed when converting date and/or time from character string

When I used the example given above I received an error that DATE_FORMAT is not a recognized built-in function name. This is my WHERE statement:

where institut.inst_cod = nmedu.inst_cod

and institut.homeinst = 0

and nmedu.credits <> 0

and nmedu.soc_sec = '#param.socsec#'

and nmedu.date_rec < '#param.grad_date1#'

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices