Julian date - always 364 1

[sciclunam]

In mysql I have 2 date fields. Dates are stored in the format yyyy-mm-dd eg: 2004-11-26

I needed a condition where if the difference between the 2 date fileds is less than 5 days "NEW" appears. So i thought that if I convert the dates to a Julian dates I can compare them. The problem is that for any date the Julian date is always 364! I am converting it using

date("z",$Row['w_date'])

What is wrong with this? Thanks

Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[sciclunam]

RE ABOVE...Correction

I am comparing a date field in the table with todays date. I want that if today's date is greater than the date field by 5 days or less then ...my statement runs.

I am using
<? if (date("z",$Row['u_date']) - date("z") <5): ?>
etc...

As I said above the problem is that date("z",$Row['u_date'] is always 364 even with different dates!

Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[Olavxxx]

DATEDIFF() was added in MySQL 4.1.1.

Why dont you do a calculation in the query?

SELECT foo,bar, DATEDIFF(`W_DATE`, NOW()) AS date_diff

then you can do in php:

if ($row['date_diff'] <= 5) {
    echo "<img src=\"images/new.gif\" alt=\"New!\" />";
  }

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[sciclunam]

I wrote a query...

SELECT b_name, b_surname, b_town,g_name, g_surname, g_town,u_date,w_date,message, DATEDIFF('w_date', NOW()) AS date_diff
FROM fweddings where date_format(w_date, '%M')='January' and date_format(w_date, '%Y')='2004' order by w_date,b_name

But I am getting the error...

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( 'w_date', NOW( ) ) AS date_diff
FROM fweddings
WHERE date_


Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[cdlvj]

The php will not work as it returns number of days since the 1st.

See mktime and create integers representing time, and compare those. 5 days should be 5*24 hours*60min*60sec

 

[Olavxxx]

I think this is your error:
DATEDIFF('w_date', NOW()) AS date_diff

you put w_date in '', not in `` <- backward quote-looking thingies.

Try this code:

DATEDIFF(`w_date`, DATE_FORMAT(NOW(), '%Y-%c-%e')) AS date_diff

if you put the w_date in the '' quotes, mysql will think it's a string.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[sciclunam]

SELECT b_name, DATEDIFF('u_date', NOW()) AS date_diff FROM fweddings where date_format(w_date, '%M')='January' and date_format(w_date, '%Y')='2004' order by w_date,b_name

Using the above in my sql gave me the error...

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( 'u_date', NOW( ) ) AS date_diff FROM fweddings WHERE date_

I also tried `u_date` , "u_date" and 'u_date'

Can you help me locate this error! This is the only thing keeping me from uploading the new feature on my website





Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[Olavxxx]

ok, let's first try a simple one:

SELECT b_name, DATEDIFF(`w_date`, DATE_FORMAT(NOW(), '%Y-%c-%e')) AS date_diff FROM fweddings;

does this work?

eg. check the returned values for date_diff, are they integers?

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[sciclunam]

Error

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( 'w_date', NOW( ) ) AS date_diff
FROM fweddings
WHERE date_


Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[sciclunam]

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( `w_date` , DATE_FORMAT( NOW( ) , '%Y-%c-%e' ) ) AS date_


Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[Olavxxx]

I think your problem is that yuor mysql version is pre 4.1.1, so you have to do it an alternative way:

SELECT TO_DAYS( NOW( ) ) - TO_DAYS( `your_date_field` ) AS `alias_fieldname`
FROM `yourtable`;

I think this way should work.

btw. for future reference, doing a datediff(), I think I should have suggested CURTIME() instead of now() wrapped in date_format.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster