0

I am making a library database. I have a column called "ArriveDate", and the dates are strings in the format MM/DD/YYYY (month and day might be single digit).

I want to find the new arrivals, so I need to find all books with the largest ArriveDate. I don't have any knowledge of SQL, so any help is appreciated!

Example table:
title, author, ArriveDate

Harry Potter 1, J.K. Rowling, 7/1/2020
Harry Potter 2, J.K. Rowling, 7/1/2020
Harry Potter 3, J.K. Rowling, 7/1/2020
Book Title 1, Author Name 1, 6/1/2020
Book Title 2, Author Name 2, 6/1/2020
Magazine 1, Author Name 3, 6/1/2020

Expected result:
title, author, ArriveDate

Harry Potter 1, J.K. Rowling, 7/1/2020
Harry Potter 2, J.K. Rowling, 7/1/2020
Harry Potter 3, J.K. Rowling, 7/1/2020

Here, 7/1/2020 is the largest date

To clarify, I'm stuck because the dates are stored as strings. If there was a way to convert them into SQL dates, I would know how to go from there.

Joe C.
  • 397
  • 4
  • 15
  • 6
    Fix your table! Do not store dates as a string! Store dates using the built-in data types. – Gordon Linoff Jul 16 '20 at 20:17
  • 2
    what is your dbms? – Fahmi Jul 16 '20 at 20:17
  • 3
    We love that you're here with us and we will help you debug your code after you make an attempt. If you don't have any knowledge of SQL, this might not be the place to start learning. – nicomp Jul 16 '20 at 20:18
  • If you store your date as a date data type, finding the most recent date should be simple – devlin carnate Jul 16 '20 at 20:23
  • I can't change how the date is stored. It's used in so many places that changing it is not my call. I am stuck because the dates are strings. If they were actual dates, then I would just use MAX and it would be easy. – Joe C. Jul 16 '20 at 20:26
  • "the dates are strings" Probably this is the thing you should change. `DateTime` type is created for a reason. – Eric Jul 16 '20 at 20:29
  • How does your database record multiple authors for a book? – Honeyboy Wilson Jul 16 '20 at 20:29
  • "I can't change how the date is stored" Then you are stuck. How can you do date arithmetic with a string??? – Eric Jul 16 '20 at 20:30

3 Answers3

2

As others have mentioned, you should probably fix your table to store dates properly. If you are stuck with your current table setup, you could try Cast('7/1/2020' as datetime) and order that way, then you could use a Common Table Expression to grab the top one, via a partition statement.

joeschmoe54321
  • 73
  • 4
0

I can't add comments, but is the same that Tarik, with sample from mysql...

SELECT * FROM Table1 where dateold = (select max(dateold) from table1)

Please check http://sqlfiddle.com/#!9/49f657/25

MobilitySol Uruguay
  • 31
  • 3
-1

You could use a subquery. Something like

select * from mytable where CONVERT(varchar, arrdate, 1) = (select max(CONVERT(varchar, arrdate, 1)) from mytable)

This is a link to a similar question: How to use MAX() on a subquery result?

Tarik
  • 10,810
  • 2
  • 26
  • 40