Page 1 of 1

SQL Statement to Fetch Last Record Ordered By Date and Time

Posted: Sat Jun 12, 2010 9:51 pm
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.

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

Posted: Mon Jun 14, 2010 10:43 am
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

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

Posted: Mon Jun 14, 2010 11:01 am
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.

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

Posted: Mon Jun 21, 2010 11:59 am
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.