How do you select an exact date in SQL?

Question

select o.customerid, c.customername, o.orderdate
from orders as o, customers as c
where o.orderdate='1997-08-26';

Using the sample northwind db, I can't quite figure out what is wrong? I have used the format of the date that is used in the sample table.

I am trying to extract the ID and name of anyone that placed an order on the 26th.

Can you please explain "what is wrong" so people don't think you meant to have a cross join? — Aaron Bertrand, Jul 03 '13 at 20:06
In other words, we don't know "what is wrong" - does the query return an error? The wrong results? No results? Explain the actual problem. — Aaron Bertrand, Jul 03 '13 at 20:13
apologies. so when I ran this, no records were returned even though there was a result in the table. — Stl, Jul 03 '13 at 20:34
@SeetalGandhi this is most likely because of the data type of `orderdate`. See ypercube's comment on FreshPrince's answer — Lamak, Jul 03 '13 at 20:37
tried that lamak but didn't work either. I dunno. As I said complete novice. — Stl, Jul 03 '13 at 20:48
@SeetalGandhi Look, I'm not a mind reader, I don't really know what query you are running against what data. If you are still trying the w3schools link that was filtering by the customer name ('Alfreds Futterkiste'), then it's really simple to realize that there are no orders made by that customer on the `Orders` table, so the query **should** return no rows — Lamak, Jul 03 '13 at 20:51

score 6 · Answer 1 · edited May 23 '17 at 10:25

6

You need to JOIN the orders and customers tables to each other:

select o.customerid, c.customername, o.orderdate
from orders as o, customers as c
where o.orderdate='19970826'
AND o.customerid = c.customerid

Using explicit syntax:

SELECT o.customerid, 
       c.customername, 
       o.orderdate 
FROM   orders AS o 
       JOIN customers c 
         ON c.customerid = o.customerid 
WHERE  o.orderdate = '19970826'

You should also read about explicit vs. implicit JOIN syntax.

edited May 23 '17 at 10:25

Community

1
1

answered Jul 03 '13 at 19:46

Kermit

33,827
13
85
121

2

If `orderdate` is `DATETIME` (and not `DATE`), then the Where has to be adjusted to: `WHERE o.orderdate >= '19970826' AND o.orderdate < '19970827'` – ypercubeᵀᴹ Jul 03 '13 at 20:07

score 1 · Answer 2 · answered Jul 03 '13 at 19:54

1

This method will allow the query to use an index on orderdate:

SELECT o.customerid, 
       c.customername, 
       o.orderdate 
FROM   orders AS o 
       JOIN customers c 
         ON c.customerid = o.customerid 
WHERE  o.orderdate >= '1997-08-26' 
AND o.orderdate < '1997-08-27'

answered Jul 03 '13 at 19:54

Steve D

580
3
4

(!) The expressing delete from table where datecolumn > "2022-10-05 04:33:10" got me deleted the whole table, even those records that had less date than in the string. You could have guessed that I needed only 1 exact record to delete... – Ivan Silkin Oct 05 '22 at 01:38

Yugz · Answer 3 · 2013-07-03T19:54:51.360

-3

try this

select o.customerid, c.customername, o.orderdate from orders as o inner join customers as c on o.customerid=c.customerid where CONVERT(VARCHAR(10), o.orderdate, 120)='1997-08-26';

Updated with Join

edited Jul 03 '13 at 19:54

answered Jul 03 '13 at 19:49

Yugz

677
1
10
23

1

This isn't taking into account the fact that there is no `JOIN` condition – Lamak Jul 03 '13 at 19:49
SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Alfreds Futterkiste'; – Stl Jul 03 '13 at 19:50
@SeetalGandhi What are you saying?, we are not mind readers. Did you check FreshPrince's answer?, it should work for you – Lamak Jul 03 '13 at 19:53
yep read fp's answer but wasn't sure why when using customer name on w3 site no join is used but results are still returned? – Stl Jul 03 '13 at 19:54
@SeetalGandhi if by using "w3" you mean w3Schools, then those links are heavily prone to error. Please post the link that you think it works, and just test FP's answer first – Lamak Jul 03 '13 at 19:56
@SeetalGandhi See?, a wrong query. Please check that the results for that one are 196 rows, wich are **all** of the rows on the `Orders` table. Does that seems right to you?, not all of the orders were made by the same customer. That query is wrong, at least if you want to do something with logic, period – Lamak Jul 03 '13 at 20:03
your query (SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Alfreds Futterkiste') will work but it is not the correct way to join tables... that is why my initial answer to your question did not have a JOIN. your problem was with comparing dates ... – Yugz Jul 03 '13 at 20:04
1

@Yugz No, it wasn't. Didn't you read my previous comment?, the query might return results, but they are ilogical, the right way to do it is **with** the join condition. – Lamak Jul 03 '13 at 20:06
I see. complete beginner so learning. so I take it w3 is not the best site to learn from? – Stl Jul 03 '13 at 20:06
@SeetalGandhi [plenty of discussions on meta about it](http://meta.stackoverflow.com/search?q=w3schools). While not explicitly blacklisted, it does not have a very good reputation. – Aaron Bertrand Jul 03 '13 at 20:08
@Lamak sorry didn't read your previous comment but i totally agree with the the results will be incorrect but the query will execute – Yugz Jul 03 '13 at 20:09
[http://www.w3resource.com/sql](http://www.w3resource.com/sql/tutorials.php) is much better than w3schools. – ypercubeᵀᴹ Jul 03 '13 at 20:10
does anyone know of a similar site that goes through step by step and try at examples? – Stl Jul 03 '13 at 20:12
@Yugz no problem. Just making sure that op understood what was wrong with the way he was using joins – Lamak Jul 03 '13 at 20:13

How do you select an exact date in SQL?

3 Answers3