I am trying to query a tale containing a column with type datetime in the format yy-mm-dd hh:mm:ss

when displaying I should truncate the time. Should only get the yy-mm-dd as 'Mon dat, year' (ex. 'Jan 4, 2003) format.

Is it possible with the SQL itself. Atleast to truncate the time part?

You can get the date formatted the way you want with the function DATE_FORMAT. Use it like this:

SELECT DATE_FORMAT(datetime_col, "%M %e, %Y") FROM table1;

thanks, it works if I query the db seperately.

But when I construct this into a SQL statement inside a jsp page, it is not.

How do I specify the format?.

my statement looks like this

<%
String dateStart = request.getParameter("dateStart") ;
String dateEnd = request.getParameter("dateEnd") ;
String sql = "select firstname, email, (date_format(loginpw.expires_dtime, %M, %D, %Y )), amount"
+ " from loginpw pw "
+ "left join payments pt on pw.loginpw_id = pt.loginpw_id "
+ " where expires_dtime >= " + dateStart
+ " and expires_dtime <= " + dateEnd ;
Result result = csuser.execute(csuser.newStatement(sql));

%>

Hi,

You need to escape the quotes around the "%M, %D, %Y" value in your statement.

Try something like...

...String sql = "select firstname, email, (date_format(loginpw.expires_dtime, \"%M, %D, %Y\" )), amount"
+ " from loginpw pw "...

Chrs,
Ash

Thanks it works.

Something more I need. How do I only show the first three letters of a month.

Now it shows 'November"

I ony need "Nov'

----

Hi,

You can replace...

%M

...with...

%b

This is the abbreviated month value.

http://www.mysql.com/doc/en/Date_and...functions.html

You may find it easier to use java's SimpleDateFormat class (or similar)?

Chrs,
Ash