string to date

[ttuser4]

Hi,

I have database with 'timestmp' field varchar(45) containing PHP timestamp (for example - May 19, 2009, 12:58 pm).

Is there any way to convert data type from varchar to timestamp without loosing existing data?

Or can I run a query like:
"SELECT * FROM projects WHERE DATE(TIMESTMP) IN ('$date1','$date2') AND USERID='$userid' AND STATUS='A' ORDER BY ID DESC";
with 'timestmp' field varchar(45)?

 

[r937]

a php timestamp is an integer, whereas "May 19, 2009, 12:58 pm" is a string

if it's the former, stored as a number in a VARCHAR column, you can convert it to an actual datetime value with the FROM _UNIXTIME functon

if it's the latter, you can convert the string to an actual datetime value with the STR_TO_DATE functon

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ttuser4]

well, the timestamp is first displayed on a page, than this value is saved into a mysql database and becomes a string.

as another feature/page, I want a user to be able to search records by entering time period (for example, 2009/10/01 2009/10/31, or 10/01/09 10/31/09 or whatever format would work with mysql query)

I tried:
SELECT ..., STR_TO_DATE(`TIMESTMP` '%d,%m,%Y'), ... FROM `projects`

but only NULL instead of date for all records.

 

[r937]

STR_TO_DATE(`TIMESTMP` '%d,%m,%Y')

two errors...


first, you're missing the comma between the two parameters of this function

second, '%d,%m,%Y' corresponds to '19,05,2009', not 'May 19, 2009, 12:58 pm'



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ttuser4]

Thank you, this is the right syntax - STR_TO_DATE(`TIMESTMP`, '%M %d,%Y') AS MYDATE