[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Nested subquery in DATE_SUB

Posted on 2006-05-11
10
Medium Priority
?
629 Views
Last Modified: 2006-11-18
Hi you all,
I'm trying to get the last 2 weeks entries in a table.
I was thinking to use a DATE_SUB and a subquery which returns a Date as it's Date argument, in the WHERE clause.
Something like:
SELECT DATE_SUB((SELECT MAX(orDate) FROM tblOrderReleases), INTERVAL 18 DAYS).
The resulting Date of the DATE_SUB function is used as one of the dates in a BETWEEN statement.
The SubQuery works. That I know.
OK, it's a bit more than 2 weeks, but that is all right as well.
I think I just can't use a SubQuery inside the DATE_SUB function.

Any ideas as to how to get a range of dates between  MAX(orDate) and  MAX(orDate) - 18 Days?

Thanx,
Guy
0
Comment
Question by:Etzie
  • 5
  • 3
9 Comments
 
LVL 16

Expert Comment

by:ellandrd
ID: 16656308
SELECT * FROM yourtable WHERE yourdatetimefield>='2003-01-01 00:00:01' AND yourdatetimefield<='2003-02-28 23:59:59';

or for example get the number of general days between the two using datediff()
0
 

Author Comment

by:Etzie
ID: 16656384
Hi,
You use a known date as a starting point (2003-01-01 00:00:01).
The whole point of my question is whether I can use a subquery inside Date_Sub, in order to obtain an UNKNOWN date as a my starting point.
I'm sorry, but I'm not sure how does you unswer help me.

Thanx,
Guy
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 16656397
>>I think I just can't use a SubQuery inside the DATE_SUB function.

why not?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 16

Expert Comment

by:ellandrd
ID: 16656456
once your DATE value from:

SELECT MAX(orDate) FROM tblOrderReleases

is in a format of "YYYY-MM-DD 00:00:00" you have nothing to worry about...

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
0
 

Author Comment

by:Etzie
ID: 16656468
I found My error.
I used DAYS in DATE_SUB instead of DAY.

Thanx mate
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 16656491
oh ok - well spotted!

is this question then closed?

ellandrd
0
 

Author Comment

by:Etzie
ID: 16656507
yeh
Your help is appreciated though, mate.
Enjoy your weekend
0
 
LVL 16

Expert Comment

by:ellandrd
ID: 16656534
ok :-(
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16688543
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 16 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question