Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

SYSDATE, sort results by date....

Hi..

I've got a table in my data base with different music gig's in it...
what I want to do is have two different querys whereas I can see all the up coming gigs and all the past gigs...

So I need to...

Select * from GIG
where (The system date is the same or lower than the gig date)... This will show all the upcoming gigs and...

Select * from GIG
where (The system date is the same or higher than the gig date)... This will show all the past gigs...

also wondering how to format the date so it doesn't come out like this > 'YYYY/MM/DD   00:00:00.00' and I want it to come out like this >'DD/MM/YYY' and no time at the end....

many thanks
0
jasocke2
Asked:
jasocke2
  • 5
  • 4
1 Solution
 
danrosenthalCommented:
Like this?

<CFQUERY>
     Select * from GIG
     where gig_date > '#dateformat(now(),'mm/dd/yyyy')#'
</CFQUERY>

0
 
jasocke2Author Commented:
nearly...

An error comes up...

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.  
0
 
jasocke2Author Commented:
This works....

<CFQUERY>
     Select * from GIG
     where gig_date > now()
</CFQUERY>


But it displays the answers like this...

Friday 2006-03-31 00:00:00.0

where I want it like this...

Friday 31-03-2006 (with no time!!)

is there anything in access I could change?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
danrosenthalCommented:
I am assuming you are using an access database, which is a little quirky with dates. Try this...

<CFQUERY>
     Select * from GIG
     where gig_date > #createodbcdate(dateformat(now(),'mm/dd/yyyy'))#
</CFQUERY>
0
 
danrosenthalCommented:
When you are outputting use #dateformat(mydbcolumnnamefordate,'dd/mm/yyyy')#
0
 
jasocke2Author Commented:
danrosenthal you are a genuis...
0
 
danrosenthalCommented:
Thanks for the ego boost!
0
 
jasocke2Author Commented:
;)

You dont happen to know about session variables do you...

The site I'm doing, you log in say add a gig, and then if you want to go back to the main screen you have to put your password in again (i had to set it up to do this because it kept coming up with arrors saying variable username password not defied in form) and I want to set it up so after a certain amount of in-activity it logs you out...
also so you cant access any of the pages without loggin in first because if you just type the address of the page  you want, http://....etc etc you can get on it, by-passing the login.cfm screen!!
Thanks again...
0
 
danrosenthalCommented:
yeah, session variables aren't that complicated
use:
<CFAPPLICATION sessiontimeout="#createtimespan(0,2,0,0)#" setclientcookies="Yes" name="sitename">

You can post another question if you have any problems
Good luck!
0
 
RCorfmanCommented:
I would consider not passing a variable from coldfusion at all and using the systemdate already in the database...
in Oracle, it would be
<CFQUERY>
     Select * from GIG
     where gig_date > sysdate
</CFQUERY>

The same can be done in access
<CFQUERY>
     Select * from GIG
     where gig_date > now()
</CFQUERY>

If you can do a query without passing in parameters from coldfusion, this is always better.
0

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.

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