Results 1 to 6 of 6

Thread: truncating time

  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: truncating time

    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?

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: truncating time

    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;

    Originally posted by bobwilli
    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?

  3. #3
    Join Date
    Nov 2003
    Posts
    7

    Re: truncating time

    Originally posted by aus
    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));

    %>

  4. #4
    Join Date
    Jul 2003
    Posts
    35
    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

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    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'

    ----

    Originally posted by asherh
    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

  6. #6
    Join Date
    Jul 2003
    Posts
    35
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •