Date/timestamp problem

[beezer]

I have a simple Table:

+----------------------+-----------------------+
| d                    | e                     |
+----------------------+-----------------------+
| 8/19/2002 8:52:32 AM | 8/19/2002 10:31:32 AM |
+----------------------+-----------------------+

These date/timestamps come from ASP and/or ASP.NET (we use ASP, ASP.Net + MySQL at the place I work at. I want to calculate the difference between the two dates in seconds, however, I cannot get MySQL to recognize the datestamps...

Like:
SELECT DATE_FORMAT(d,"%Y") FROM dah

returns:
Null

My Question is how do i get the asp datestamp (its actually the now() function from asp & asp.net) that MySQL will recognize, so I can calculate the amount of time between the two datestamps? I've tried every date function on the mysql.com site, all to no avail. I'm about to just go through date stamp and use string functions to get it formatted correctly, but that looks to be rather insane, and would be a hella-huge line of sql.

It just might be easier to not do it in MySQL... I hope it doesn't come to this though.

Any help would be greatly appreciated!

Scott

[hand]

Is it possible to use PHP or only mySQL?

[beezer]

Nope. We're stuck with the systems we got here. It just might be easier to do this on the page instead of the MySQL Server Side.

I think its rather stupid that MySQL doesn't accept alternative date formats, rather than the YYYY-MM-DD HH:MM:SS. I mean, I'm sorry, MySQL rules, but it sucks that they don't have the capability to do different date and time formats.

I tried this... but still stuck with the same problem:

SELECT LEFT(d,INSTR(d," ") - 1) AS "date", RIGHT(d,INSTR(d," ")) AS "time", DATE_FORMAT(LEFT(d,INSTR(d," ") - 1),"%Y") AS "year" FROM dah

+-----------+------------+------+
| date      | time       | year | 
+-----------+------------+------+
| 8/19/2002 | 8:52:32 AM | NULL |
+-----------+------------+------+

Scott

[hand]

In combination with php it's simple

<?php
function makeMySQLdate($asp) {
	$datTimAPM  = explode(" ",$asp);
	$dat		= explode("/",$datTimAPM[0]);
	$tim		= explode(":",$datTimAPM[1]);
	if ($datTimAPM[2] == "PM") {
		$tim[0] = "1".$tim[0];
	}
	$asp = $dat[2]."-".$dat[0]."-".$dat[1]." ".$tim[0].":".$tim[1].":".$tim[2];
	return $asp;
}
mysql_connect("localhost", "", "") or die("could not connect"); // Connect zur DB
mysql_select_db("test"); // Datenbank auswählen

$abfrage = "SELECT d,e FROM dah";
$ergebnis = mysql_query($abfrage)
	or print "&gt;&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

while($row = mysql_fetch_array($ergebnis)){
	$fromDate = makeMySQLdate($row[d]);
	$toDate   = makeMySQLdate($row[e]);
	$berechne = "SELECT FLOOR((UNIX_TIMESTAMP('$toDate') - UNIX_TIMESTAMP('$fromDate'))/60) as minutes";
	$ergebnisi = mysql_query($berechne)
		or print "&gt;&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

while($rowi = mysql_fetch_array($ergebnisi)){
	print "Difference between $row[d] and $row[e] are $rowi[minutes] minutes<br>";
}
}
?>

[beezer]

That works too.. If anyone knows how to do it within the query itsself, it'd be appreciated.