Solved

QofQ Date Convert Error

Posted on 2003-12-02
3
208 Views
Last Modified: 2013-12-24
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
Comment
Question by:newstandard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9860331
In your actual code is this ever returning more than 1 row?

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

Accepted Solution

by:
PE_CF_DEV earned 250 total points
ID: 9860377
nevermind that Ixsee the error now..here is the fix:
<CFSET QuerySetCell(testQ,"dateCol",dateformat(testQuery1.SomeDate1)&' '&timeformat(testQuery1.SomeDate1))>
0
 

Author Comment

by:newstandard
ID: 9860419
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question