• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1802
  • Last Modified:

ISDATE working but ISNULL not working (as expected)?

I'm new to CFML so please excuse my ignorance.
I'm experimenting with ISDATE and ISNULL on a simple mysql table which as some NULL dates:
-- ----------------------------
-- Table structure for `temp`
-- ----------------------------
DROP TABLE IF EXISTS `temp`;
CREATE TABLE `temp` (
  `id` int(11) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO `temp` VALUES ('1', null);
INSERT INTO `temp` VALUES ('2', '2011-04-13');
INSERT INTO `temp` VALUES ('3', null);
INSERT INTO `temp` VALUES ('4', '2011-04-21');
INSERT INTO `temp` VALUES ('5', null);
INSERT INTO `temp` VALUES ('6', '2011-04-24');
INSERT INTO `temp` VALUES ('7', null);
INSERT INTO `temp` VALUES ('8', '2011-04-28');
INSERT INTO `temp` VALUES ('9', null);

Open in new window

I then run the following in CFML:
<cfquery name="testing">select id, date from temp</cfquery>
<p>Temp table:</p>
<cfoutput query="testing">
#id# IsDate(date)=#IsDate(date)# IsNull(date)=#IsNull(date)#<br />
</cfoutput>

Open in new window

and get the following output:
Temp table:

1 IsDate(date)=NO IsNull(date)=YES
2 IsDate(date)=YES IsNull(date)=YES
3 IsDate(date)=NO IsNull(date)=YES
4 IsDate(date)=YES IsNull(date)=YES
5 IsDate(date)=NO IsNull(date)=YES
6 IsDate(date)=YES IsNull(date)=YES
7 IsDate(date)=NO IsNull(date)=YES
8 IsDate(date)=YES IsNull(date)=YES
9 IsDate(date)=NO IsNull(date)=YES

Open in new window

ISDATE works correctly, giving NO for NULL dates and YES for real dates.
What I do not understand is why ISNULL gives YES for all items -- I was expecting the opposite of ISDATE -- YES for NULL dates and NO for real dates.
What have I missed?
0
ShanghaiD
Asked:
ShanghaiD
  • 5
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please try to `date` instead of date in the query
0
 
ShanghaiDAuthor Commented:
select `id`, `date` from `temp`

still gives same result.......
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
<cfquery name="testing">select id, date, IsNull(date) is_null, IsDate(date) is_date from temp</cfquery>
<p>Temp table:</p>
<cfoutput query="testing">
#id# IsDate(date)=#Is_Date# IsNull(date)=#Is_Null#<br />
</cfoutput>

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ShanghaiDAuthor Commented:
No,, that throws an error.  Function IsDate is a CFML function, not a MySQL function, so it cannot run in the cfquery tag.  
I have already confirmed that the odd id date values are NULL in MySQL, just to be sure, and running (in MySQL) simply:
SELECT id, date from temp
results in:

1      (null)
2      2011-04-13
3      (null)
4      2011-04-21
5      (null)
6      2011-04-24
7      (null)
8      2011-04-28
9      (null)
(as it should).

0
 
ShanghaiDAuthor Commented:
I also ran a simple cfdump of the query and got (as expected):
 <cfdump var="#testing#" abort="true">
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I am not a CF specialist, actually (rather mysql)
<cfquery name="testing">select id, date, IsNull(date) is_null from temp</cfquery>
<p>Temp table:</p>
<cfoutput query="testing">
#id# IsDate(date)=#IsDate(date)# IsNull(date)=#Is_Null#<br />
</cfoutput>

Open in new window

0
 
ShanghaiDAuthor Commented:
Your revised query gives correct output but it uses the MySQL IsNull function (in the query) rather than the CFML ISNULL function in handling the results of the query.

It seems to me that the CFML ISNULL function is treating the "empty string" as something other than a Null, and so is not showing output that I had expected.

I'll do some more research tomorrow at work (it's evening now in Shanghai).

Thanks for you input already.
0
 
gdemariaCommented:
You have to think of CF's new isNull() function much like  structKeyExists()

It is not testing to see if a database "NULL" value is NULL, coldfusion always handles NULL from the database as an empty string.  isNull() is not testing for empty strings, it is testing for the non-existance of a variable; such as IsDefined() and structkeyexists()

If you want to test if something is empty, the most efficient way is to use len()

<CFIF len(myQuery.value)>
   There is a value
<cfelse>  
   It is empty
</cfif>


Here is more reading...

http://www.bennadel.com/blog/1654-Learning-ColdFusion-9-IsNull-And-Working-With-NULL-Values.htm

0
 
ShanghaiDAuthor Commented:
Thank you for your insight. I learn something new about CFML every day!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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