Datediff

DateDiff
I have to write an app that interacts with mySQL (I really must have done
some evil, evil stuff in a previous life to be landed with this!)

I need to work out the difference in days between values in the database and
the current date. "No problem," thought I , "I'll just use the SQL DATEDIFF
command." Heh! Well, the user interface I'm using didn't even recognise
DATEDIFF as being a function, so I decided to visit the mySQL website.

Their description of DATEDIFF is as follows:

------------------------
DATEDIFF(ARGUMENTS)
TIMEDIFF(ARGUMENTS)
[Rest of description to be added here]

NEED EXAMPLE
DATEDIFF() and TIMEDIFF() were added in MySQL 4.1.1.
------------------------

---

DATEDIFF
when I, SELECT xx,stamp,xx FROM my_table WHERE DATEDIFF(CURDATE(),DATE(stamp)) <1

I get a syntax error... at the (CURDATE(),DATE(stamp)) I fail to see what is wrong with it. I am on 4.0.25

---

DATEDIFF
Considering I have a table named business where I store advertisementes of business to sale. Each advertisement belongs to an advertisement category wich haves different timelimits (10 days, 20 days, 30 days....) to expire counting from de date it's published. Why does the following query work perfectly in MySQL 5.0 and not in MySQL 4.1.18 ? How could I do for it to work?

SELECT business.field, count(business.field) FROM business, advertisements WHERE business.advertype=advertisements.IDAN AND (DATEDIFF(CURDATE(), business.publish_date)<=advertisements.timelimit OR business.advertype=6) AND business.situation='1' GROUP BY business.field;

---

DateDiff() Function
I am trying to use the MSSQL function DateDiff('m', date1, date2) in MySQL but mysql do not support the type 'm' or 'd' or 'yyyy'. I do not want to receive the datediff value as a SELECT field e.g.

"SELECT datediff('m',dateofbirth,'2005/06/23') from Dependant WHERE TranID = 3068"

---

Datediff Alternative
I am using this query update bookings

set num_days =(select datediff(course_end,course_start) as num_days);

which works fine locally as my version is up to date. My webspace is running an earlier version of mysql. Is there any way of changing this to run on an earlier version of mysql.

---

DATEDIFF In MySQL 4.0
I need a DateDiff function in 4.0! But it wasn't added until 4.1. Does anyone know of what they used before 4.1 came around?

---

DateDiff Syntax And Use
I want SQL to tell me the numbers of days between a selected collum and today.

From what I've read on the web it SHOULD be something like this

select *, DATEDIFF('day', renewal_date, getDate())....
I've also tried to get it to work using NOW(), but surrounding renewal_date and getDate with DATE().

It keeps telling me I have a syntax error around 'day', renewal_date, getDate()

now it gets 1 step more complicated after I get this to work, What I really need is instead of using all the renewal_date's I need MAX(renewal_date). Will this complicate the matter further?

---

Datediff Replacement In MySQL
here is my table

create table dailyregister (
recordid int,
SignedIn datetime,
SignedOut datetime
TotalTime ???)

TotalTime is calculated upon request of the operator and it should be equal to subtracting SIgnedOut - SignedIn.

What datatype is best suited for the job? I tried datetime as well but I only get 0000-00-00 00:00:00 results.

I need to get the time difference between those two fields (in seconds or minutes) and I found out that datediff ()function only works in MySQL 4.1.

---

How To Using Datediff Not Include Saturday And Sunday
Let say, i've this following table

tblapplication
ID | Name | ReceivedDate | ProcessDate
---------------------------------------------------
A009 | Mikaela | 2007-07-06 | 2007-07-11

Then, me do the SQL bewlo to know 'How many days it take to process the application'
select ID,Name,
datediff(ProcessDate,ReceivedDate) as 'NoOfDaysTaken_ToProcess'
from tblapplication

Then the results, shown follows:-
ID | Name | NoOfDaysTaken_ToProcess
---------------------------------------
A009 | Mikaela | 6


My problem is...
How to filter NoOfDaysTaken_ToProcess not include Saturday and Sunday? As a result, the results will get this

Results
ID | Name | NoOfDaysTaken_ToProcess
---------------------------------------
A009 | Mikaela | 4

---

Inserting Result Of Datediff Into A Field
I am trying to get the output of a datediff query on a recordset to update a field in the same record. The following

select * from bookings join (select datediff(course_start, course_end) as num_days from bookings where personid >= 1) as dt;

appends another field num_days onto the bookings table, where the field already exists. I wish to update the field num_days in the table not put another field. I know that joining them is not the way to go, but this is the only way I got the correct result.

---

REQ: MySQL 4.0 Equivilent Of "DateDiff("m", Date, Now())=1"
I have a query that works in the rest of the SQL world

"SELECT invoice.*, invoice.Date
FROM invoice
WHERE (DateDiff("m", Date, Now())=1);"

which will give me all of last month's invoices.

However it doesn't work with MySQL 4.0.

While

"SELECT
Invoice.*
FROM
invoice
WHERE
(Month(Invoice.Date) = ((Month(Now()))-1))"

Is a close substituent, it will blowup in January.

Anyone have an equivalent expression for MySQL 4.0?

---