When I display the timestamp all I get is the date how do I get the time as well?
Are you referring to when you run a SQL Query? If so you can either configure your SQL client to display a specific date time format, or you can also specify the format in your SQL Query.
For example, you can use the to_date() function to specify the format for a give timestamp column.
SELECT to_date(timestamp, 'YYYY.MM.DD HH24:MI') --this will get you Year, Month, Day and then Hours and Minutes
Thank you for your help, this is what I am doing"
JOIN BBLEARN.ACTIVITY_ACCUMULATOR AA ON (U.PK1 = AA.USER_PK1)
JOIN BBLEARN.COURSE_MAIN CM ON (AA.COURSE_PK1 = CM.PK1)
INNER JOIN BBLEARN.COURSE_CONTENTS CC ON (AA.CONTENT_PK1 = CC.PK1)
U.USER_ID = 'aarodriguez19'
AND AA.TIMESTAMP > '27-AUG-2017'
AND AA.TIMESTAMP < '11-OCT-2017'
ORDER by AA.TIMESTAMP, AA.PK1
I get the timestamp in year/month/day but still no time, What am I doing wrong can you tell?
I am using OpenDB
Hi Cyndi, try this:
and(aa.timestamp between to_date('08/27/2017 00:00:00','mm/dd/yyyy HH24:MI:SS')
AND to_date('10/11/2017 23:59:59','mm/dd/yyyy HH24:MI:SS'))
ORDER by AA.TIMESTAMP, AA.PK1;
instead of converting the date in the select statement.
If you are still having any issue with this, try using to_char instead of to_date. The to_date converts strings to dates but the to_char will convert the date to a string. We use this in a lot of the reports so that we get a consistent look for dates.
Another option for people here is to set the preferences of your db client. Like so in sqldeveloper you can access Tools > Preferences > Database > NLS to set the date format. I usually adjust this after first install and also from time to time make use of the TO_DATE function.
If you have need to display multiple time/date formats for different reports or data sources and setting the client default you can also set up snippets to set the date format:
Retrieving data ...