0

I have 2 tables, 1 table has a date column, 1 table has a time column. I want to have date and time works seperatedly. This is what i use :
ALTER SESSION SET nls_date_format='dd/mm/yyyy';

I use this for the 1st table and then use this for the 2nd table :
ALTER SESSION SET nls_date_format='hh24:mi';

But it doesn't work right. When i do the select * from it all changes back to hh24:mi type. How can i have date and time seperatedly ?

Improve this question
3
  • Altering anything for the session alters the entire session, not a single table. Why would you think otherwise? Commented Apr 15, 2016 at 17:45
  • What do you need? Are you worried about what is stored in the database, or what is displayed on screen? Oracle database DOES NOT store date without time (it defaults to 00:00:00 if time is not provided) and it DOES NOT store time without date (it defaults to first day of current month if time is provided without date - DON'T ASK ME WHY they made that choice for default and not, for example, CURRENT date!) Commented Apr 15, 2016 at 18:06
  • Oracle doesn't have a time data type, so what data types are your columns, what do they contain, what does your query show now, and what do you want it to show for that data? It isn't a good idea to rely on NLS settings or use select * anyway, except (maybe) for ad hoc queries. Why do you want to store date and time separately, if that is what you are trying to do? Commented Apr 15, 2016 at 18:12

1 Answer 1

Reset to default
1

As noted Oracle always has a date and time. If you want to see and use just the date or just the time you could use, for example to only work with the time, TO_CHAR(ColumnA, 'HH24:MI:SS')

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.