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
jasocke2Asked:
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.

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

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
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
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.