Date Comparison to SQL Datetime

I didn't realise you could wrap CF fundctions "DateFormat" around data from a query ie dbDate.

Are you sure you can do this.

Yah!  Of course, I'm really... really... sure 'coz I have already tried using it.  In fact, I have been implementing this to one of my projects which is "Web Account System".

By the way, you can actually remove the symbols "#" in between the CF DateFormat function.

So, it would be :
  Where DateFormat(dbDate,'yyyy-mm-dd') = <cfqueryparam cfsqltype="cf_sql_date" value="#yourDate#">

Haven't you tried this before ???


Regards!
eNTRANCE2002 :-)

I'll give it a whirl, but cant see how this could possibly work. Even with the #'s

By the way Plucka, I mistakenly give you the information.  

Actually, you should remove the symbols "#" coz it will be expected to encounter an error.  The DateFormat function that we are using here is not the CF DateFormat function but it's the SQL DateFormat function.

This is the syntax :
  DATEFORMAT ( datetime-expression, string-expression )

In which the string expression is just like the same as the "mask" that we are setting on the CF DateFormat function.

Hope this makes clear to you.


Best regards!
eNTRANCE2002 :-)

That's lovely,

But according to the docs and my testing.

DateFormat is not a SQL function.

I didn't think you could run a CF function on SQL query columns.

This is the solution I went with.

and      StartDateTime >= '#DateFormat(frmWorkDate, "YYYY-MM-DD")# 00:00'
and      StartDateTime <= '#DateFormat(frmWorkDate, "YYYY-MM-DD")# 23:59'

Actually you can simply do it this way :

AND StartDateTime between '#DateFormat(frmWorkDate, "YYYY-MM-DD")# 00:00' and '#DateFormat(frmWorkDate, "YYYY-MM-DD")# 23:59'

Try it.

By the way Plucka, I just want to have a clarification.  

Are you going to get only those records which has StartDateTime value within the range from "YYYY-MM-DD 00:00" to "YYYY-MM-DD 23:59"???

How about if the StartDateTime value is beyond that range but the same date?  
Like for example :
  'YYYY-MM-DD 23:59:00.1'
  'YYYY-MM-DD 23:59:01'
It will not be returned...  Test it for confirmation.

It is much better if you do your condition like this :
  AND StartDateTime = '#DateFormat(frmWorkDate, "YYYY-MM-DD")#'

Since you want to get all records for a single date as what you have specified on your main post(QUESTION).


Best wishes!
eNTRANCE2002 :-)

The best way to compare DATE with DATETIME is to use DATEDIFF, since it takes care of time differences.  Something like this (syntax may vary dependently on actual DBMS in use.  For today:

SELECT * FROM MyTable WHERE DATEDIFF(d, datetime_field, '#DateFormat(Now(), "mm/dd/yyyy")#')=0

If you are using MS SQL and need the comparison in the database to just the date protion and not the time then something like this works very well

convert(varchar,DateColumn,101)

this will turn the date into the format mm/dd/yyyy that can then be ccompared against another date.

I think what you want to use is a SQL function convert.
You can format the database datetime field and compare at the same time.

<cfquery name="mystuff">
select * from table
where convert(varchar(12),database_date, 101) = #single_date#

</cfquery>

101 is the formating used for the date time conversion  which equals : "mm/dd/yyyy"

You can find this info in SQL books online under: "Convert and Cast"

wolffy

for the format you want: 'YYYY-MM-DD'
use:
where convert(varchar(10),database_date,120) = #single_date#



wolffy