What is the best way to compare dates with SQL

[cjbeck71081]

I was wondering what you guys do to prepare dates for SQL, and then when they come out of SQL how to prepare them for analysis.  I have always been pulling dates together using a string, and then poping them into SQL, and when they come out of SQL i have been decoding them... i'm sure there is a better way.

 

In other words... someone signs up for something, i want to time stamp thier sign up.  I would populate the now(); date into the sql, but i want to make sure its in "DATE" format to fit my SQL syntaxt.  And if i wanted to send them an email with the date they signed up, I would format it to read "January 15th 1980".  Without hacking it up.

 

Thanks in advance

Chris

[cooldude832]

php strototime and mktime are useful from a php end

 

mysql has built in functions to rewrite dates (similar to php's date()) for formfitting and compassion

[cjbeck71081]

Thanks for your reply, im looking now, i found a nice way to insert into SQL the current date.  But now, coming out of SQL the format is 2008-08-09.  Again, I know i can hack it, but im looking for the most straight forward way to either rearrange the numbers to read 08-09-2008 or convert them altogether and say August 09, 2008.

 

I'll look at strtoday and mktime.

[cjbeck71081]

i used: "INSERT into date (date) VALUES (CURDATE());" to insert the current date into my DB

 

Then i used "SELECT * FROM date";

 

I made a variable $date from $row['date'];

 

When I populated echo(date("F j, Y", $date));

 

It gave me December 31, 1969.  Any ideas?

[Zane]

the most straight forward way I've ever used is the UNIX timestamp..

as cooldude mentioned..PHP has functions to manipulate these timestamps in sooooo many ways..with no hacking

 

also,......mysql has functions to play with them as well.

 

 

 

When I populated echo(date("F j, Y", $date));

 

It gave me December 31, 1969.  Any ideas?

date works the UNIX timestamp.

CURDATE does not return a UNIX timestamp.. it returns a 2008-06-13 type date...

 

the reason it's giving you that mundane date (december 1969) is because you need to convert the 2008-06-13 to a UNIX timestamp with strtotime first before it will work.

 

Why did it give you that exact date?....because um..

UNIX timestamp only go as far back as Jan 1 1970.  and the number you gave it was less than a regular normal......datestamp

 

I hope I answered thoroughly enough

[fenway]

Don't store timestamps in the DB -- use a proper DATETIME field.

 

DATE_FORMAT() will let you return the date how ever you see fit.

[Zane]

I stand corrected