I have the below table and when i run this query:

FROM table1
WHERE Date <= "11/15/2018 12:00:00 AM"

it only retrieves the dates 15,14,13,12,11,10 and 1 of Nov 2018 . How can i solve this? Thanks!

I tried changing the format of the date in the WHERE part of the query to "11/15/18 12:00:00 AM", it doesn't work.

enter image description here

  • Is this for SQL Server or MySQL? They're different. Jan 5 '19 at 14:15
  • @LowlyDBA To be honest, i don't know, but it's the same sql that i type in Microsoft Access queries, if that helps.
    – Neek
    Jan 5 '19 at 14:18
  • You will need to ask the owner of the database then what the expected dataset should be. Your results are all less than or equal to that date, so unless you are missing rows, this is working as expected.
    – clifton_h
    Jan 5 '19 at 14:29
  • 2
    Please be very careful to use unambiguous date/time formats. Both SQL Server and humans can misconstrue 11/12/18 as December 11th or November 12th under various circumstances. 20181112 is unambiguous and language-safe. Jan 5 '19 at 17:08
  • @AaronBertrand Thank you for your note :)
    – Neek
    Jan 5 '19 at 18:12

This looks like a case where the date column isn't a date at all, but rather a string.

You aren't getting Nov 2 through Nov 9 because the sorting on the string and calculating if it's less than 11/1.... in alphanumeric order.

Ideally, dates will be stored as dates, and then your existing query will work. This will also ensure that dates are valid dates, and you don't end up with data for 32 February, or even random strings.

If you can't change the type of the column, you'll have to cast the database values to a date to do the comparison (which will hurt performance, but get you the results you expect).

  • You, sir, are a very smart person. The operation i want to do is on a much bigger database, something like 18 columns and 1 million rows and i have imported it into Microsoft Access as a text file, but i provided this small two column example to simplify matters. My bad, i should have mentioned importing it as strings. Now i know why it doesn't work. Thank you so much.
    – Neek
    Jan 6 '19 at 17:46
  • @Neek I've learned this the hard way... If this was the problem, please mark the answer as accepted to signal to future readers that this helped. 👍
    – AMtwo
    Jan 6 '19 at 23:04

MySQL does not honor AM.

MySQL wants dates to be specified Year/month/day.

You can format output to be dd/mm/yy if you like, using DATE_FORMAT, but do not expect anything to recognize that ordering.

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.