CFIF with dates

This should be a simple one to answer, but I don't have a lot of ColdFusion skills and have a job that requires me to maintain and occasionally improve on existing ColdFusion apps. And I'm stuck on this...

I'm trying to add a CFIF in the output section of my page so that it won't display a date if the one in
the database is 07/04/1776 (that's what gets put in my databases on import when a date was not there when we got the data). If the date in the database is 7/4/1776 I want it to display "NA" or something like that -- even blank would be fine.

So I've tried this and it doesn't seem to be working:
<CFIF DateFormat(dob, "mm/dd/yyyy") is not "07/04/1776">
#DateFormat(dob, "mm/dd/yyyy")#
<cfelse>
"NA"
</CFIF>

I can't seem to find any references on this online or in the one ColdFusion book that I have. I'd appreciate any advice on how to fix this. I'm assuming it's something with how I list 07/04/1776 in the code (with quotes? without quotes?, etc)

thank you in advance.
MaryJo

 

Where exactly are you using this code:

<CFIF DateFormat(dob, "mm/dd/yyyy") is not "07/04/1776">
#DateFormat(dob, "mm/dd/yyyy")#
<cfelse>
"NA"
</CFIF>

Code (markup):

I think you can take care of this within your sql statment.

Heres an example:

<cfquery name="" datasource="">
SELECT EXAMPLE_DATE_COLUMN
FROM DATE_TABLE
WHERE EXAMPLE_DATE_COLUMN <> "7/14/1776"
</CFQUERY>

Code (markup):

This should exclude any dates that equal the date you put in the where clause in your sql statment.

Is ther an error message your getting from coldfusion when you run your cfif statment?
If so post it.

 

I don't want to use it in the SQL because I want to return all records regardless of what the date says. I just want to DISPLAY the date field in my results only if the date is not 7/4/1776. So right now I have it in the Output section. I have other pages that use CFIF in the output section in this manner, except none of them refer to dates. They are usually something like if this field isn't Null, then display it.

 

Use a date (not string) comparison:

<cfif dateCompare(dob, "1776-07-04", "d") neq 0>....<cfelse>.....</cfif>

Code (markup):

Well you could still use SQL and return all records. Just use a CASE statement, not a WHERE clause filter. The syntax is database dependent, but for MS SQL, somethin like:

SELECT    CASE WHEN dob = convert(datetime, '1776-07-14', 120) THEN NULL ELSE dob END AS Dob, ....  FROM   TableName 
....
<cfif IsDate(DOB)>#DateFormat(dob, "mm/dd/yyyy")# <cfelse> "NA"</cfif>

Code (markup):

 

hi

i don't find anything wrong with ur code. its working fine like this even if your db date is " 07/04/1776" or" 7/4/1776"
<cfoutput>
<cfset dob="7/4/1776">
<CFIF DateFormat(dob, "mm/dd/yyyy") is not "07/04/1776">
#DateFormat(dob, "mm/dd/yyyy")#
<cfelse>
"NA"
</CFIF>
</cfoutput>

 

Even if it does work, it's not a good idea to use anything other than a date comparison. CF doesn't have much concept of types. So you can get seemingly weird results because CF is doing some sort of data type conversion you can't see.

The best solution is to use dateCompare() to compare two dates - not strings.

 

So ... what was the outcome? ;-)