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

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.
0
Plucka
Asked:
Plucka
  • 5
  • 4
  • 2
  • +4
1 Solution
 
Renante EnteraCommented:
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#">
</CFQUERY>

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
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.
0
 
Renante EnteraCommented:
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 :-)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
PluckaAuthor Commented:
I'll give it a whirl, but cant see how this could possibly work. Even with the #'s
0
 
Renante EnteraCommented:
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 :-)
0
 
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.
0
 
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'
0
 
Renante EnteraCommented:
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.
0
 
Renante EnteraCommented:
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 :-)
0
 
hartCommented:
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#">
</CFQUERY>

if its MS SQL u have convert()

http://www.sqlteam.com/item.asp?ItemID=240

Regards
Hart
0
 
INSDivision6Commented:
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
0
 
mrichmonCommented:
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.
0
 
wolffy18Commented:
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
0
 
wolffy18Commented:
for the format you want: 'YYYY-MM-DD'
use:
where convert(varchar(10),database_date,120) = #single_date#



wolffy

0
 
raGadirajuCommented:
Please note that if the column is indexed, the exec plan will not use that index because of the date function..
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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