SQL this day one year ago

One option is to create the date one year ago as a variable and use that in your query

example:

<?
$oneYearAgo = (date("Y")-1)."-".date("m-d 00:00:00"); // Creates a datetime
$sql = "select * from orders where orderDate>'$oneYearAgo'");

?>

This is if orderDate is a datetime (YYYY-MM-DD HH:MI:SS).

Batalf

Hi qwertq,

Here you go:
    SELECT *, DAY (orderDate), MONTH(orderDate) FROM orders WHERE DATE_ADD(NOW(), INTERVAL -1 YEAR) = orderDate

---
German Rumm.

baltf: i was really hoping to get the work done in the query.

ger: that did not seem to work. here is what i tried:
select * FROM phoneCart WHERE DATE_ADD(NOW(), INTERVAL -1 YEAR) = orderDate

also, is there no command for current day number in sql? that would really work best for me, because this is part of a function that also looks up week last year, year to date and month to date, all with same function to eliminate duplicate coding.

maybe something similar to DAYOFYEAR(NOW()), is there a DAYOFMONTH?

i am high. there is

try looking at the DATE_FORMAT function...
it provides a number of ways of parsing a date
e.g. DATE_FORMAT(mydate,"%Y")

the function for the current date is CURDATE(), time is CURTIME(), both in tandem is NOW()

so if you want find records that are a year old...

SELECT * FROM phoneCart WHERE ( DATE_FORMAT(CURDATE(),"%Y") = DATE_FORMAT(mydate,"%Y") + 1) AND
(DATE_FORMAT(CURDATE(),"%d") = DATE_FORMAT(mydate,"%d"))

Different databases use different Date/Time functions - which one are you using?

--saddy1--

i used DAYOFMONTH...

good point... I usually assume MySQL + PHP.  The format he's using looks more like T-SQL