Skip to main content
Stevievegas49
Participant
December 12, 2012
Question

datetime parameter in coldfusion report builder

  • December 12, 2012
  • 2 replies
  • 2395 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 is closed to new replies. Start a new post to keep the conversation going.

2 replies

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 & ")"

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

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