Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Unanswered: Strip time out of a datetime column

    Hi there,

    in SQL Server's TSQL I can use something like:

    SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)

    to get rid of the time portion of a datetime column, e.g. set it to midnight.
    What is the equivalent in MySQL?

  2. #2
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142
    Hi, I'm a newbie and did my date/time parsing in PHP but here's a clipping from the MySQL manual that might or might not help (I don't know enough to know)...

    ---- mysql manual --------------------------------------

    If format is given, the result is formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function.


    mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    -> '%Y %D %M %h:%i:%s %x');
    -> '2003 6th August 06:22:58 2003'

    GET_FORMAT(DATE|TIME|TIMESTAMP, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')
    Returns a format string. This function is useful in combination with the DATE_FORMAT() and the STR_TO_DATE() functions. The three possible values for the first argument and the five possible values for the second argument result in 15 possible format strings (for the specifiers used, see the table in the DATE_FORMAT() function description).
    Function Call Result
    GET_FORMAT(DATE,'USA') '%m.%d.%Y'
    GET_FORMAT(DATE,'JIS') '%Y-%m-%d'
    GET_FORMAT(DATE,'ISO') '%Y-%m-%d'
    GET_FORMAT(DATE,'EUR') '%d.%m.%Y'
    GET_FORMAT(DATE,'INTERNAL') '%Y%m%d'
    GET_FORMAT(TIMESTAMP,'USA') '%Y-%m-%d-%H.%i.%s'
    GET_FORMAT(TIMESTAMP,'JIS') '%Y-%m-%d %H:%i:%s'
    GET_FORMAT(TIMESTAMP,'ISO') '%Y-%m-%d %H:%i:%s'
    GET_FORMAT(TIMESTAMP,'EUR') '%Y-%m-%d-%H.%i.%s'
    GET_FORMAT(TIMESTAMP,'INTERNAL') '%Y%m%d%H%i%s'
    GET_FORMAT(TIME,'USA') '%h:%i:%s %p'
    GET_FORMAT(TIME,'JIS') '%H:%i:%s'
    GET_FORMAT(TIME,'ISO') '%H:%i:%s'
    GET_FORMAT(TIME,'EUR') '%H.%i.%S'
    GET_FORMAT(TIME,'INTERNAL') '%H%i%s' ISO format is ISO 9075, not ISO 8601.


    mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
    -> '03.10.2003'
    mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
    -> 2003-10-31

    GET_FORMAT() is available as of MySQL 4.1.1. See section 14.5.3.1 SET Syntax.
    HOUR(time)
    Returns the hour for time. The range of the return value will be 0 to 23 for time-of-day values.


    mysql> SELECT HOUR('10:05:03');
    -> 10

    However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.



    mysql> SELECT HOUR('272:59:59');
    -> 272

  3. #3
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    Interesting problem. I think you are looking to run an update query against the MySQL table. Here is an example I put together:

    UPDATE table_name SET datetime_field_name = DATE_FORMAT(datetime_field_name,'%Y-%m-%d');


    It seems to do the job quite efficiently. It has the effect of blanking the time portion of the datetime field (actually it set it to "00:00:00") which is midnight that morning.

  4. #4
    Join Date
    Jun 2003
    Posts
    7
    Aah, that is even easier than in T-SQL. Thank you both!

Posting Permissions

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