compatibility of date format

sir
i have a teble in a database with fields
1.id
2.tdate (dd/mm/yy)
3.title
how do i check for equality

compatibility of tdate with system date)
select * from table where tdate = system date //----> some other format
is there any way to make them both compatible with each other so they could be checked for equality.

Every database has there own way of doing this. Oracle provides a date function called trunc, which you can indicate the precision level that you want for a given date value. Everything beyond that precision is truncated:
eg. the same day in Oracle:
"select id from emp where trunc(hiredate) = trunc(sysdate)"
OR
"select id from emp where to_char(hiredate, 'dd/mm/yy')=to_char(sysdate, 'dd/mm/yy')"

Formatting for MSAccess can be done like this as well
"SELECT id FROM emp WHERE format(hiredate, 'dd/mm/yy') = format(now,'dd/mm/yy')"
Jamie

Hi
what does the format function do ? maybe it could help me.
I Use MS Access as you can see in the insert statement below how I insert the date in to the database, I realy don't no why you have to enter it in such a cryptic way, all the other ways I tryed didn't work, now I have another problem when I do an update on a table in MS Access I can not update the date column even if I use the number sign (#), if anyone could tell me how to do this I would greatly apprecaite it.
st.execute( "insert into com values( " + invoiceT.getText() + "," +"#" + dateC[0].getSelectedItem() + "/" + dateC[1].getSelectedItem() + "/" + dateC[2].getSelectedItem() + "#" + "," + sm + "," + sales1T.getText() + "," + co1 + ", 0, 0, " + total + ")" );
thanks in advane
Yoel
------------------
Sun Certified Programmer for JAVA 2 Platform

what is your error message? you may have a very different problem than ammuswar(the original poster). Does the query work in a SQL editor session in M$Access? What is does the query really look like:
String query = "insert into com values( " + invoiceT.getText() + "," + "#" + dateC[0].getSelectedItem() + "/" + dateC[1].getSelectedItem() + "/" + dateC[2].getSelectedItem() + "#" + "," + sm + "," + sales1T.getText() + "," + co1 + ", 0, 0, " + total + ")" ;
System.out.println(query);
Then cut and paste the printout into the MSAccess SQL editor.
let me know how it turns out.
Jamie

Hi Jamie,
first of all thank you very much for replying to my post, my problem is when I use the update statement as follows.
This statement below is what I got from my program after I did what you told me, I cut it and paste it into the MSAccess SQL editor.
and I got this error message: Syntax error in UPDATE statement.
UPDATE com
SET date = #Aug/22/2001#, salesid = 1006, Amount1 = 456.78, Commission1 = 0.09, Amount2 = 524, Commission2 = 0, Total = 41.1102
WHERE Invoice = 123
but if I take out these words :: date = #Aug/22/2001#, :: I don't gate any error messages, I hope you will be able to help me out with this problem ?
thanks Jamie
Yoel
[This message has been edited by yoel stern (edited August 23, 2001).]

I think your problem is in #Aug/22/2001# portion. It will not accept the Aug. Try inserting using #08/22/2001# instead.
It works for me.
Jamie

hi jamie u have been kind enought to show me the correct path
i sincerely thank u
i wonder if u know java and servlets cos i have a code which u can see and correct it i having problem in the dates there
i am sending in the code
--------------------------------------------
see my table (title) in seq server database is :
id tdate (datetime-datatype) title
1 22/08/2001 svsdfdfj asdfjefke
2 23/08/2001 ddadkjdkdfj sddeD
.. ............... ...........................
20 10/09/2001 dfdfjskjasdk isdfjdfj
now when i say select * from title;
result
id tdate title
1 2001-08-22 00:00:00.000 xcxcdcj adafvsdsdf
now my requirement
clicking on todays content link i should get todays information(ex. if today is 22/08/2001 i should get the content of 22/08/2001)
how do u do this
using date a = new date(); stores a format whiich is not compatible with tdate
so select * from title where tdate = a; ------error
please correct this and check the code also ....cos u know i am in probabtion period so i will not get a second chance
once confirmed i will not bother u

import java.util.Date;
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class DisplayServlet extends HttpServlet {
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
Date todaysdate = new Date();
out.print("<html><head>");
out.print("</head><body>");
// -----------------------------------------is the syntax correct for ---i mean this i how u write it in servlet program------
out.print("Todays content");
//out.print("<form action=\"");<br /> //out.print( req.getRequestURI() );<br /> //out.print("\" method=\"post\">");
//out.print("<input type=\"submit\" ");<br /> //out.print("value=\" \"> ");
//out.print("Display Records</form>");
out.print("</body></html>");
out.close();
}
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
String a = req.getParameter("param");
out.print("<html><head>");
out.print("</head><body>");
out.print("<code><pre>");

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc dbc dbc_exmp,userid,pwd");
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM title where tdate='a'");
while(rs.next()) {
out.print(rs.getObject(1).toString());
out.print("\t");
out.print(rs.getObject(2).toString());
out.print("\t\t");
out.print(rs.getObject(3).toString());
out.print("\n");
}

} catch (SQLException e) {
throw new
ServletException("Servlet Could not display records.", e);
} catch (ClassNotFoundException e) {
throw new
ServletException("JDBC Driver not found.", e);
} finally {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
if(con != null) {
con.close();
con = null;
}
} catch (SQLException e) {}
}
out.print("</pre></code>");

out.print("</body></html>");
out.close();
}
}
this is my code---------------------------
now my problem

i have a table in SQL-SERVER named as title
this table has three fields
1.id
2.tdate----this is of the type datetime
3.content
i am using JRUN 3.0
i want to make a java servlet in which u have a text link
Date todaysdate = new Date();
todays content;
In the servlet :
i am accepting this parameter as
String a = request.getParameter("param");

i will the database connecion---------------------
then
'
select * from title where tdate = 'a';

-----------but this not showing any result
error;
there is something wrong here in he compatitbility of 'a' with tdate

Hi Jamie,
I tryed as you told me it still did not work, I think the problem is with the column name, the column for the date is named date, I'm gona check this out I will let you know, if I work this out.
Thanks Again
Yoel

Yoel: just got in from the weekend, so I couldn't help you

. Everything looks good EXCEPT for one little piece of SQL. You will probably hit your head against the wall a couple of times because it is pretty simple! This is the line that is causing you problems:
rs = stmt.executeQuery("SELECT * FROM title where tdate='a'");
this code will try and find any date that matches the letter a. Probably not what you want. You want any date that matches the value stored in variable a. (Also, you need to change your single quotes to # signs):
rs = stmt.executeQuery("SELECT * FROM title where tdate=#"+ a +"#");
Hope this works,
Jamie

[This message has been edited by Jamie Robertson (edited August 27, 2001).]