Deducting dates for between SQL

[shocker-z]

Hi,

 

Brief of what i want to do

I have dates in database for logged date and closed date, im querying them to pull out stats for the month which i can do find with string but hoiw could i makesure then i check the dates between say 2007/04/07 and 2007/05/01 automaticaly so that say if i loaded the page in january it would deduct a month off the last day of the current month so it would look like 2007/12/01 and 2008/01/01.

 

Hope someone gets what i mean butfor some reason i cant get it in my head how to explain

 

so a quick example..

 

 

If todays date is 2007/05/30 then i wil;l be looking to pull up data from between 2007/05/01 and 2007/06/01 which will cover the entire months data. how can i automaticaly deduct 1 month off the current and add a month to sleect the 1st of next month?

 

I'm sure it's easy but in my old age and all... lol

 

 

Regards

Liam

[Barand]

SELECT ... WHERE datecol BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()

[obsidian]

If I'm not mistaken, you always want to grab from the 1st of this month through the 1st of next month, correct? If so, try something like this:

<?php
$start = date('Y-m-d', mktime(0,0,0,date('m'), 1, date('Y'));
$end   = date('Y-m-d', mktime(0,0,0,date('m')+1, 1, date('Y'));
$sql = "SELECT * FROM tableName WHERE dateCol BETWEEN $start AND $end";
?>

[Barand]

~shocker-z,

 

Remember that BETWEEN is inclusive, so you will get May's data plus the 1st June's data.

[obsidian]

~shocker-z,

 

Remember that BETWEEN is inclusive, so you will get May's data plus the 1st June's data.

 

Thanks for that, Barand... good catch. shocker-z, here's an updated date() call that will get only this month's dates:

<?php
$start = date('Y-m-d', mktime(0,0,0,date('m'), 1, date('Y'));
$end   = date('Y-m-d', mktime(0,0,0,date('m'), date('t'), date('Y'));
$sql = "SELECT * FROM tableName WHERE dateCol BETWEEN $start AND $end";
?>

[shocker-z]

wkd thanks alot guys..

 

would you be able to either explain how to deeduct different things like days and years of just modify it to show how to say deduct 7 days so that i can see the syntax of how the deduction works as i need to do a deduction of days next..

 

Thanks alot

 

Liam

[obsidian]

If you're using PHP to do the calculations, use strtotime():

<?php
$date = date('Y-m-d');
$minusDays = date('Y-m-d', strtotime("$date - 7 days"));
$minusMon  = date('Y-m-d', strtotime("$date - 1 month"));
// ... etc
?>

 

As much as is possible, try to use MySQL to do date calculations:

SELECT CURDATE() - INTERVAL 7 DAY;
SELECT CURDATE() - INTERVAL 1 MONTH;
-- ... etc

 

Good luck.

[hennysav]

Actually I can't use PHP as language, only MySQL queries and I am stuck with one which I more or less found a solution but not a satisfactory one. I want to deduct two dates and the result needs to be larger smaller than 9 months.

 

I have this at the moment

 

SELECT p.personID as cPersonID, p.lastname as cLastname, p.firstname as cFirstname,  p.living, father.personID

AS FatherNr, father.birthdate AS FatherBirthdate,

YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age, father.deathdate as Father_death, p.birthdate as cBirthdate, CONCAT(

ROUND(

(YEAR(p.birthdatetr)+MONTH(p.birthdatetr)/12+DAY(p.birthdatetr)/356)

-

(YEAR(father.deathdatetr) + MONTH(father.deathdatetr)/12+DAY(father.birthdatetr)/365)

)*12, " Months")

 

(yes I want to check if children are born within nine months after their father died)

 

The above query gives a few false results. Anybody has better ideas?

[hennysav]

Actually found the solution. I hope this helps someone else too

 

SELECT p.personID as cPersonID, p.lastname as cLastname, p.firstname as cFirstname,  p.living, father.personID

AS FatherNr, father.birthdate AS FatherBirthdate,

YEAR( p.birthdatetr ) - YEAR( father.birthdatetr ) AS Father_age,

father.deathdate as Father_death, p.birthdate as cBirthdate,

CONCAT(ROUND(DATEDIFF(p.birthdatetr,father.deathdatetr)/30), " Months")

AS dif_month, p.deathdate,  p.gedcom, p.changedby

FROM tng_children AS ch

LEFT JOIN tng_people AS p ON ( ch.personID = p.personID

AND ch.gedcom = p.gedcom )

LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID

AND ch.gedcom = f.gedcom )

LEFT JOIN tng_people AS father ON ( father.personID = f.husband

AND father.gedcom = f.gedcom )

 

WHERE p.birthdatetr <> "0000-00-00"

AND father.birthdatetr <> "0000-00-00"

AND p.deathdatetr <> "0000-00-00"

AND father.deathdatetr <> "0000-00-00"

AND

 

DATEDIFF(p.birthdatetr,father.deathdatetr)  > 360

 

ORDER by cBirthdate, cLastname, cFirstname, dif_month