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

ColdFusion Mysql date query problems

Ok.  I have a table in Mysql that has a field that is a date datatype named due_date.  As you probably already know, Mysql date fields store dates in the format yyyy-mm-dd.  I need to run a query from coldfusion that finds dates in this field that are 30 days older than today.  In coldfusion now() - 30 should accomplish this.  I can't seen to get the query right to accomplish this.  I have done this in Oracle before but the date field in Oracle is formatted differently.  I need to know how to structure this query.
0
BLWedge
Asked:
BLWedge
  • 3
  • 2
1 Solution
 
CrazeeCommented:
SELECT field FROM table WHERE due_date = '#DateFormat( DateAdd( 'd' , -30 , now() ) , 'yyyy-mm-dd' )#'
( perhaps you don't have to enclose date in single quotes )

or use sql's DATEADD - this is Ms-SQL how ever, so the function may differ...

SELECT field FROM table WHERE due_date = DATEADD( Day , -30 , getDate() )

cheers
0
 
jyokumCommented:
CFQUERYPARAM usually solves most date/time problems with databases

<cfquery ...>
SELECT field FROM table WHERE due_date = <cfqueryparam value="#DateAdd('d',-30,now())#" cfsqltype="cf_sql_date">
</cfquery>
0
 
anandkpCommented:
yes - cfqueryparam shld do it for u !

if u want all the records in the DB for last 30 days ...

<cfquery name="x">
SELECT field FROM table
WHERE due_date > <cfqueryparam value="#DateAdd('d',-30,now())#" cfsqltype="cf_sql_date">
</cfquery>
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
jyokumCommented:
hmm??
0
 
CrazeeCommented:
Strange... you specified "30 days older than today", not at least 30 days older...
0
 
CrazeeCommented:
or rather no more than...
0
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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