[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

QofQ Date Convert Error

I'm having some big problems with using QofQ on a query that has some null values for a date-based column.  If I try to do a QofQ against it it errors.  I've illustrated several different scenarios in the code below, and only one of the test cases will error.  It is the one that gets an actual SQL Date object back from ColdFusion, uses that value to build another query from scratch, and then does a QofQ against that query.

The way my code is layed out, I don't really have the ability to change the way I'm doing things (as far as building a query from scratch and then QofQ against it) without a lot of rewinding in development, which I don't really have time for.  What can be done to quickly fix this error without changing the overall structure of the code?  Please note that in production my code is doing the thing that causes the error in the code below.  Replace #dsn# with a datasource of your own.  Please note that it is SQL Server 2000.

Is this some sort of CFMX error?  Has Macromedia released a hotfix?

Thanks,

Justin


<CFQUERY NAME="testQuery1" DATASOURCE="#dsn#">
      SELECT GETDATE() as SomeDate1
</CFQUERY>

<CFQUERY NAME="testQuery2" DATASOURCE="#dsn#">
      SELECT '2001-01-01 00:00:00.0' as SomeDate2
</CFQUERY>

<CFSET SomeDate3 = "2001-01-01 00:00:00.0">

<CFSET testQ = QueryNew("numCol,dateCol,stringCol")>
<CFSET QueryAddRow(testQ)>
<CFSET QuerySetCell(testQ,"numCol",1)>
<CFSET QuerySetCell(testQ,"stringCol","First row")>

<!--- Does cause error
<CFSET QuerySetCell(testQ,"dateCol",testQuery1.SomeDate1)>
--->
<!--- Does not cause error
<CFSET QuerySetCell(testQ,"dateCol",testQuery2.SomeDate2)>
--->
<!--- Does not cause error
<CFSET QuerySetCell(testQ,"dateCol",SomeDate2)>
--->

<CFSET QueryAddRow(testQ)>
<CFSET QuerySetCell(testQ,"numCol",2)>
<CFSET QuerySetCell(testQ,"stringCol","Second row")>
<CFSET QuerySetCell(testQ,"dateCol","")>

<CFDUMP VAR="#testQ#"><BR><BR>

<CFQUERY NAME="testQuery2" DBTYPE="query">
      SELECT * FROM testQ
</CFQUERY>

<CFDUMP VAR="#testQuery2#"><BR><BR>
0
newstandard
Asked:
newstandard
  • 2
1 Solution
 
PE_CF_DEVCommented:
In your actual code is this ever returning more than 1 row?

<CFQUERY NAME="testQuery1" DATASOURCE="#dsn#">
     SELECT GETDATE() as SomeDate1
</CFQUERY>
0
 
PE_CF_DEVCommented:
nevermind that Ixsee the error now..here is the fix:
<CFSET QuerySetCell(testQ,"dateCol",dateformat(testQuery1.SomeDate1)&' '&timeformat(testQuery1.SomeDate1))>
0
 
newstandardAuthor Commented:
That looks like it does fix the test code.  Hopefully it will fix the real code when I get a chance to put it in.  Thanks.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now