Page 1 of 1

sql query/syntax for date

Posted: Tue Mar 08, 2005 9:06 pm
by nhan
anyone who can help, i dont know how to sort out the date in my database field...

one of my field is named data3 which contains this data :
05-03-09 ( 08:55:05 AM )

i used a function to get this data, the function is :
$Today = (date ("m-d-y ( h:i:s A )",time()));

my problem is how can i sort out the values of a specific date lets say
only the date 05-03-09 regardless of the time....


thanks!

Posted: Tue Mar 08, 2005 9:27 pm
by feyd
store dates in either integer form (unix timestamp) or the database's native date/time format. It's far easier to sort by it then. ;)

sql query/syntax for date

Posted: Tue Mar 08, 2005 9:36 pm
by nhan
can you give me a hint on how to do that..... thanks!

is it like this one :
$date_month = date(m);
$date_year = date(Y);
$date_day = date(d);
$time_hour = date(H);
$time_min = date(i);

thanks!

Posted: Tue Mar 08, 2005 9:37 pm
by feyd
well.. mysql stores date-time as: 'Y-m-d H:i:s'

sql query/syntax for date

Posted: Tue Mar 08, 2005 9:43 pm
by nhan
meaning.. i have to insert the dates into seperate fields???

or is there any other way in which i can select the data in the table using query just to search for the part of the string in the field let say only
05-03-09...

like this one :
select * from tbl where data3 = '05-03-09';

i tried this one but but no results...

thanks!

Posted: Tue Mar 08, 2005 9:51 pm
by feyd
if you didn't change the format which is used in the database, then no, that wouldn't work. If the field is in date/datetime format, then you can use DATE_FORMAT(). If it's still a text-type, then you'll need to use SUBSTRING()

sql query/syntax for date

Posted: Tue Mar 08, 2005 11:45 pm
by nhan
sir,

could you give me an example ofthe sql statement in inserting the date and time in different fields? i only do this one :

$query = "INSERT INTO tbl (userName,ip,data3) VALUES ('$user','$ip','$Today')";

the format of today is : $Today = (date ("m-d-y ( h:i:s A )",time()));

i could not query for the result if i am to sort the specific date....

thanks so much