By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,912 Members | 1,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,912 IT Pros & Developers. It's quick & easy.

Re: How to retrieve latest record when date and time are separate ?

P: n/a
Walt <wa**@boatnerd.com.invalidwrote in message news:<3F***************@boatnerd.com.invalid>...
JN wrote:

I record attendance of persons to table with these columns:
PERSON_ID, DATE, TIME, CODE .
Is it possible to write SQL query, which gives me latest record of all
persons? Unfortunately, the DATE and TIME are separate columns.
Thanks for any ideas.

Yes, but you'll find life a lot simpler if you just use the built in
date datatype. The best answer is to re-model your data design to take
advantage of the functionality provided by Oracle.

If you store date and time as strings and then try to manipulate them as
dates you're re-enventing the wheel, working way to hard, and probably
making some subtle mistakes along the way.
You can use below sql for your result

select * from test;

PERSON_ID DAT TIME CODE
--------- -------- ----- ----------
1 26.04.98 07:00 First one
2 08.07.03 16:00 Second
2 09.07.03 13:00 Third
2 09.07.03 11:00 Fourth

Select * from test
Where to_Date(dat||Tim,'DD/MM/YYYY HH24:MI')
=( Select Max(to_Date(dat||Tim,'DD/MM/YYYY HH24:MI') ) from test);

PERSON_ID DAT TIME CODE
--------- -------- ----- ----------
2 09.07.03 13:00 Third
Jun 27 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.