We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SYSDATE, sort results by date....

jasocke2
jasocke2 asked
on
Medium Priority
453 Views
Last Modified: 2013-12-24
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
Comment
Watch Question

Like this?

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

Author

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.  

Author

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?
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>
When you are outputting use #dateformat(mydbcolumnnamefordate,'dd/mm/yyyy')#

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
danrosenthal you are a genuis...
Thanks for the ego boost!

Author

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

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.