This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes sql syntax for system date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql syntax for system date " Watch "sql syntax for system date " New topic
Author

sql syntax for system date

ammuswar kumar
Greenhorn

Joined: Aug 19, 2001
Posts: 27
sorry guys i know that this is not the riight place to put this quest
but i hope u understand
what is the syntax in sql to compare the date(field from table) with the system date(host)
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I don't know of a "standard" sql procedure, but most databases have their own way of doing this:
Oracle- use date math(sysdate - hiredate)
Access- use the dateDiff(sysdate, hiredate) function.
if the answer returns 0 then the it is the same day.
note: This will cause portablility problems between different databases. If you can live with that, check your database documentation for date functions/math.
Jamie
Jamie
[This message has been edited by Jamie Robertson (edited August 20, 2001).]
ammuswar kumar
Greenhorn

Joined: Aug 19, 2001
Posts: 27
hi jamie
thanks for showing me the way,i am obliged but need some more clarification
ammuswar kumar
Greenhorn

Joined: Aug 19, 2001
Posts: 27
hi jamie
tell me if this is correct
select * from table
where date =sysdate;
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

>"hi jamie
>tell me if this is correct
>select * from table
>where date =sysdate;"
well that all depends how accurate you want it. That would be true in Oracle if the dates were equal to the millisecond!
to compare to the nearest day, you have to use the truncate function-->this will return all values where hiredate is equal to the system date(at the day level)
"select * from table where trunc(hiredate)=trunc(sysdate)"

Jamie

[This message has been edited by Jamie Robertson (edited August 20, 2001).]
 
Don't get me started about those stupid light bulbs.
 
subject: sql syntax for system date
 
Similar Threads
null results
SQL92 Escape Syntax
Urgent Help... SimpleDateFormat working differently in windows and unix.
Convert sql date to util date
datepart error