Truncated incorrect time value: '1:05 AM'

[crs27]

Hai All,

This is my create statement

CREATE TABLE `dbname`.`tablename` (
`vh_id` int(10) unsigned NOT NULL,
`gh_utc` time NOT NULL,
`gh_mydate` date default NULL,
`gh_serverdate_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
KEY `FK_geo_gpsdata_2` (`vh_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

when i query this table im getting "Truncated incorrect time value: '1:05 AM"
the below is the query.Not getting why the time value is truncated.

SELECT time_format(gh_utc ,'%H:%m:%s'),
date_format(gh_mydate,'%d/%m/%Y')
FROM tablename where vh_id='1' and timestamp(gh_mydate,gh_utc)
between timestamp('2009/01/01','1:05 AM') and timestamp('2009/01/16','1:05 PM') ;

Awaiting for the reply.

Thanks in advance

[Atli]

Hi.

The string "1:05 PM" is an invalid time value. MySQL uses a 24 hour "HH:MM:SS" syntax.

As a result, MySQL *truncates* the value you gave it and turns it into the most likely alternative that fits the required syntax, which becomes "01:05:00".

To get the effect you are after, you need to use "13:05" for the PM value and "01:05" for the AM value.

[crs27]

thanks for the reply Atli.
I realized that,but i did not want to add 12 in my logic and then to make it 24 hr time.
Is their any other way that i get 24hr time in the query itself.

[Atli]

You could use the STR_TO_DATE function to convert it into a valid value.

Although, I would advise you to have your front-end do this conversion instead. It's a lot cleaner that way.

[crs27]

thanks again atli.Shal follow the same.