SQL query - latest date

Posted on 2006-04-06
Last Modified: 2008-02-01
How is the SQL query for retrieving the recordset with the latest date (in the field myDate)?

The following 2 SQL queries don't work like I want them to work:

SELECT * FROM myTable WHERE myDate >= ALL (SELECT myDate FROM myTable);

SELECT * FROM myTable WHERE myDate = (SELECT MAX(myDate) FROM myTable);

Assuming there are the following dates in the table:
both queries return 31.12.2005 but they should return 06.04.2006
Question by:TheFunkSoulBrother
    LVL 54

    Accepted Solution

    That's because they aren't seen as dates because of the dots and it's probably defines as a text field.
    SELECT * FROM myTable WHERE dateserial(right(myDate,4),mid(myDate,4,2),left(myDate,2)) = (SELECT MAX(dateserial(right(myDate,4),mid(myDate,4,2),left(myDate,2))) FROM myTable);

    LVL 1

    Author Comment

    Indeed, it's defined as a text field. Your query works!! Thanks a lot!
    I thought it was defined as a date field.
    Access displays dates differently depending on the country setting - in my country dates are displayed in the format by default.
    Therefore I thought it was defined as a date field.

    If it would have been a date field my queries would have worked, right?
    LVL 54

    Expert Comment

    Yes :-)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now