asked on

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?

Guy Hengel [angelIII / a3]

could you please try to `date` instead of date in the query

ShanghaiD

ASKER

select `id`, `date` from `temp`

still gives same result.......

Guy Hengel [angelIII / a3]

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

ShanghaiD

ASKER

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).

ShanghaiD

ASKER

I also ran a simple cfdump of the query and got (as expected):

Guy Hengel [angelIII / a3]

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

ShanghaiD

ASKER

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.

ASKER CERTIFIED SOLUTION

gdemaria

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ShanghaiD

ASKER

Thank you for your insight. I learn something new about CFML every day!