1

I thought I was good with SQL till I was told to get the content of all 30 days. I do have a column called "connect_time" which has date and time in the following format: 2014-08-28 11:11:42

All my queries have been a fail till now. I tried the following https://stackoverflow.com/questions/13266130/how-to-select-last-one-week-data-from-todays-date

I just tried:

SELECT  *
FROM   Table_Name
WHERE  connect_time >= DATEADD(day,-7, GETDATE())

I cannot understand the reason its wrong and I am trying to find the syntax so I can edit it myself but I am getting confused what part of the code is syntax and what is their column names.

  • I see two tags. Is this a SQL Server question or a MySQL question ??? – RolandoMySQLDBA Sep 17 '14 at 19:43
  • Its mySQl thanks for pointing my mistake i meant to put just sql – Sizzlin' PeanutButter Sep 17 '14 at 19:46
4

Using DATE_ADD() or DATE_SUB()

SELECT * FROM Table_Name
WHERE connect_time >= DATE_ADD(CURDATE(),INTERVAL -7 DAY);

or

SELECT * FROM Table_Name
WHERE connect_time >= DATE_SUB(CURDATE(),INTERVAL 7 DAY);

Without those functions, you can also do

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() + INTERVAL -7 DAY);

or

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() - INTERVAL 7 DAY);
  • Thank you it works! After hours of trying, it works. I really need to re-learn sql – Sizzlin' PeanutButter Sep 17 '14 at 19:58
  • You just need to learn MySQL's dialect (almost like a British citiszen speaking English in the USA, a Portuguese citizen speaking in Brazil, citizen of Spain speaking in Puerto Rico, etc.). – RolandoMySQLDBA Sep 17 '14 at 20:01
1

This query runs just fine, but does not include entire days unless your time is set to midnight. This is because GETDATE() returns date and time,so it will only return rows >= to the exact moment 7 days ago that matches the time of your query.

If you are trying for whole days, perhaps:

 SELECT * FROM dbo.Table_Name
 WHERE connect_time >= CAST (DATEADD(day,-7, GETDATE())AS Date) 
    AND connect_time < CAST (GETDATE() AS Date) 
 ORDER BY connect_time

This would start at midnight of 7 days ago, but would not include today's partial data.

EDIT: GETDATE() is a SQL Server function. In MySQL is would be something like:

 SELECT * FROM Table_Name
 WHERE connect_time >= DATE (CURDATE() INTERVAL -7 DAY) 
    AND connect_time < DATE(CURDATE()) 
 ORDER BY connect_time

But I am not MySQL savvy.

  • What if it cant recognize cast? – Sizzlin' PeanutButter Sep 17 '14 at 19:47
  • The OP requires the syntax for MySQL. Yours appears to be SQL Server? – Max Vernon Sep 17 '14 at 19:51
  • That is my mistake I put SQL-server and MySQL. I just realized i am terrible w/ database. Thanks for pointing out max. – Sizzlin' PeanutButter Sep 17 '14 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.