SQL Statement to Fetch Last Record Ordered By Date and Time

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
devarishi
Forum Contributor
Posts: 101
Joined: Fri Feb 05, 2010 7:15 pm

SQL Statement to Fetch Last Record Ordered By Date and Time

Post by devarishi »

I ahve this SQL Statement to fetch only the Last Record from the specified table:

Code: Select all

$sql  = "SELECT TOP 1 * FROM tblHDParent ORDER BY logDate DESC;";
But it just doesn't work fine as it would otherwise because of the data in the logDate field:

Code: Select all

10-April-2010 - 05:15 AM
I have intentionally appended a "-" and a "time" and "AM/PM" value to the "date" value. So, I am using a Text data type of field in the table (MS-Access). Now the sorting thing doesn't work as specified by the phrase:

Code: Select all

ORDER BY logDate DESC


So, what SQL Statement can we devise so as to achive the desired result that the SQL Query on the specified data type and field must return the Last Record?

Please, note that I do have a SrNo or ID field in the table but it is not a numeric or Auto Number field. It is a Text Field and is weird by desgin. I can't rely on it at all. Besides, as it is indexed (since Primary Key Constraint indexes the column by default) the order of the Records are no longer in a chronological order.

So, I have only logDate field to work on to get the record which was last added to the table.

Because of

Code: Select all

- 05:15 AM
in the logDate field the ORDERing is not doing just what we want.

For example, when I want to fetch the last record from the current table the record for this date is fetched:

Code: Select all

9-June-2010 - 07:25 AM
even though there are records till 13th June. :crazy:
:crazy:
Any help?


Please Note: Not only viewing the Last Record is working but also displaying all the records from Latest to Oldest is not working as the column "logDate" is not being properly ORDERed. I can't change the SrNo or ID column's data type to Auto Number / Auto Incremental Type of Field because there are lots of records and dependent tables- Dependencies on this field.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: SQL Statement to Fetch Last Record Ordered By Date and T

Post by andyhoneycutt »

If you're running mysql, you can use this nifty function:

Code: Select all

STR_TO_DATE('9-June-2010 - 07:25 AM','%e-%M-%Y - %h:%i %p');
#returns:
2010-06-09 07:25:00
Maybe there's some sort of equivalent function in MS-Access? I hope to at least get you pointed in the right direction...It looks like it might be as simple as using the CDate() Function.

Hope that helps,
Andy
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SQL Statement to Fetch Last Record Ordered By Date and T

Post by Weirdan »

So, I am using a Text data type of field in the table (MS-Access).
Doesn't access have a datetime column type?

Edit: It does. You should be using that for date/time storage instead of text columns.
devarishi
Forum Contributor
Posts: 101
Joined: Fri Feb 05, 2010 7:15 pm

Re: SQL Statement to Fetch Last Record Ordered By Date and T

Post by devarishi »

I think I can leave out the hyphen "-" from the value which gets stored in the field. That way, I can easily sort the column. But there are over 150 records in the table which also need to be modified accordingly.

Any short method to remove only the 3rd hyphen from '9-June-2010 - 07:25 AM' and make such changes in all the records?
Weirdan wrote:
So, I am using a Text data type of field in the table (MS-Access).
Doesn't access have a datetime column type?

Edit: It does. You should be using that for date/time storage instead of text columns.
Post Reply