webtotaltoday
Posts: 46
|
| Posted: 06/02/2006, 5:25 AM |
|
Hi,
I am trying to find the difference between 2 dates in the URL.
I have this sql code:
select datediff ('{to}', '{from}') as total
But for somereason it gives the error
Database Error: You have an error in your SQL syntax near '('30/06/2006', '29/06/2006') as total LIMIT 0,1' at line 1
Can anyone help?
Regards
|
 |
 |
mrachow
Posts: 509
|
| Posted: 06/02/2006, 6:52 AM |
|
I found right syntax should be
DATEDIFF ( datepart , startdate , enddate )
with day for datepart for example.
_________________
Best regards,
Michael |
|
|
|
webtotaltoday
Posts: 46
|
| Posted: 06/02/2006, 8:36 AM |
|
Hi,
I tried that but still errors.
I have changed it to PHP code and it works fine
But for some reason when it collects the data from the URL for the to and from date it puts it in a yyyy-mm-dd format rather than my URL format which is dd-mm-yyyy
Is there any way i can change this?
Here is the code:
$date1 = (CCGetFromGet("from", ""));
$date2 = (CCGetFromGet("to", ""));
$string1 = strtotime($date1); // or the variable $date1
$string2 = strtotime($date2); // or the variable $date2
// Subtract the difference
$difference = $string2-$string1;
// divide by the time period you wish to check
$DifferenceInDays = $difference/86400;
$orders->fullamount->SetValue($DifferenceInDays);
|
|
|
|
WKempees
|
| Posted: 06/03/2006, 4:21 AM |
|
This works (result = 19)
select DATEDIFF('2006-05-30','2006-05-11') as total
1:So is your MySQL version 4.1.1+ ? That is when datediff() was added.
2:Is your dateformat for the database and project the same and correct?
You are feeding it dd/mm/yyyy it is expecting yyyy-mm-dd.
3: Maybe you just need to format the date you are getting from the URL into
the right (expected) fromat to make it work.
Walter
|
|
|
|
|
