[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1180
  • Last Modified:

SQL this day one year ago

ok i am working on a sql and having problems....

what i want to do is to select all rows that are from this day one year ago. here is my query:

SELECT *
FROM orders
WHERE
TO_DAYS(NOW()) = TO_DAYS(orderDate) AND YEAR(date_add(NOW(), interval '-1' year)) = YEAR(orderDate)

the year must remain seperate from the day.

thanks!
0
qwertq
Asked:
qwertq
  • 4
  • 2
  • 2
  • +2
1 Solution
 
BatalfCommented:
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
0
 
German_RummCommented:
Hi qwertq,

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

---
German Rumm.
0
 
qwertqAuthor Commented:
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.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
qwertqAuthor Commented:
maybe something similar to DAYOFYEAR(NOW()), is there a DAYOFMONTH?
0
 
qwertqAuthor Commented:
i am high. there is
0
 
virmaiorCommented:
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"))



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

--saddy1--
0
 
qwertqAuthor Commented:
i used DAYOFMONTH...
0
 
virmaiorCommented:
good point... I usually assume MySQL + PHP.  The format he's using looks more like T-SQL
0
 
saddy1Commented:
If DAYOFMONTH() works, then YEAR() should also work, so it would be something like:

WHERE YEAR(thedate) = YEAR(NOW())-1

In PostGres, you can use EXTRACT:
WHERE EXTRACT(YEAR FROM thedate) = EXTRACT(YEAR FROM NOW())-1

There's also date_part:
date_part('year', NOW())

You get the idea ;-)  Check your database's documentation (under date/time functions) to find the one that works.

--saddy1--
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now