Hi,
I am using below query to get the most recent date from mdb.
select max(date(exam_date)) from exam
mdb has following dates :-
1/7/2010 1:44:38 PM
1/7/2010 11:44:39 AM
1/7/2010 12:44:37 AM
1/7/2010 9:44:36 AM
application returns 1/7/2010 9:44:36 AM instead of 1/7/2010 1:44:38 PM ( it does not work for time).
however it works for the date, when following date exist in mdb:-
1/7/2010 1:44:38 PM
4/7/2010 11:44:39 AM
7/7/2010 12:44:37 AM
6/7/2010 9:44:36 AM
it returns 7/7/2010 12:44:37 AM
Can you please provide me the solution for this.
Thanks,
Srinivas
|
>select max(date(exam_date)) from exam
You should use select max(exam_date) from exam , Because all rows will return 2010-01-07 after you call date(exam_date) in example1.
|
Hi,
Thanks, this resolved my issue.
having another issue ..
in another table of mdb , date is stored as number
Ex: 39672.442373
i am using following query to convert number to date
select date(max(acq_time)) from exam
its giving only date but not time.
Can you please advice?
Thanks,
Srinivas
|
>select date(max(acq_time)) from exam
>its giving only date but not time.
select timestamp(max(acq_time)) from exam
|
Thank you
the following query
select timestamp(max(acq_time)) from exam
gives below output...
for the value 39672.442373 it gives timestamp as 2008-08-13 00:00:00.0
for the value 39673.442373 it gives timestamp as 2008-08-14 00:00:00.0
we are seeing all zeroes for time in above.
Are these accurate values or do we need to manipulate them.
Thanks,
Srinivas
|
>Are these accurate values or do we need to manipulate them.
Checked. The older package will round and get a Date type. You can try
select timestamp(max(acq_time)*86400000- 2209161600000) from exam
|
thank you.
this works , it returns date time as 2008-08-13 23:49:01.027 for value 39673.992373
can we have this converted to 2008-08-13 11:49:01 PM
|
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss a"); //sdf.setTimeZone(java.util.TimeZone.getTimeZone("GMT")); System.out.println(sdf.format(ts));
|