We are on IB 3.3.0.
I have two sqls -
1.
select distinct a11.BOOK_RELEASE_DT BOOK_RELEASE_DT,
FROM_UNIXTIME(a11.BOOK_RELEASE_DT) DATE_ID
from DEV_DOLDWDDB.LU_BOOK a11
sample records -
988700400 5/1/2001 12:00:00 AM
1201593600 1/29/2008 12:00:00 AM
1167120000 12/26/2006 12:00:00 AM
926751600 5/15/1999 12:00:00 AM
2.
select a11.book_release_dt book_release_dt,
max(FROM_UNIXTIME(a11.book_release_dt)) DATE_ID
from QA_DDB_DM.LU_BOOK a11
group by a11.book_release_dt
981014400 2001-02-01 00:00:00
1123052400 2005-08-03 00:00:00
1263283200 2010-01-12 00:00:00
1239692400 2009-04-14 00:00:00
May I please know why do I get difference in the second column i.e. for the first one I get 5/1/2001 12:00:00 AM (12:00:00 am)
and in the second the format is different (00:00:00).
The results above are from directly executing the sql against the db.
We also use a Reporting tool called Microstrategy and it produces correct result (gives correct Book release date in date format) if I use the first sql and incorrect when I use the second one (get some long number instead of correct value in date format).
Thanks so much for your help and please let me know if I could provide more details.
Please note that you might notice different db qualifier but the db version and table structure for both of them are identical.
Best Regards,
Som
