Date Comparison to SQL Datetime

How to you do a date comparison to a datetime.

ie: Have a datetime in a database and want to get all records for a single date.
LVL 18
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Renante EnteraSenior PHP DeveloperCommented:
Hello Plucka!

You can do something like this :

<CFQUERY name="qGetRecord" datasource="DSN">
  Select * From TableName
  Where #DateFormat(dbDate,'yyyy-mm-dd')# = <cfqueryparam cfsqltype="cf_sql_date" value="#yourDate#">

Hope this helps you.  Just try it.

eNTRANCE2002 :-)
PluckaAuthor Commented:
I didn't realise you could wrap CF fundctions "DateFormat" around data from a query ie dbDate.

Are you sure you can do this.
Renante EnteraSenior PHP DeveloperCommented:
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 ???

eNTRANCE2002 :-)
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

PluckaAuthor Commented:
I'll give it a whirl, but cant see how this could possibly work. Even with the #'s
Renante EnteraSenior PHP DeveloperCommented:
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 :-)
PluckaAuthor Commented:
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.
PluckaAuthor Commented:
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'
Renante EnteraSenior PHP DeveloperCommented:
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.
Renante EnteraSenior PHP DeveloperCommented:
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 :-)
which database are u using...

if its oracle use to_char() function

<CFQUERY name="qGetRecord" datasource="DSN">
  Select * From TableName
  Where To_Char(dbDate,'dd/mm/yyyy') = <cfqueryparam cfsqltype="cf_sql_date" value="#yourDate#">

if its MS SQL u have convert()


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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


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#


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"

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


Please note that if the column is indexed, the exec plan will not use that index because of the date function..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

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.