Let's say I have a data table that has columns A-Z and column C is for Date-Time. I want to see all of the columns in my table. When I do a "Select * from datatable" the return is the standard DD-Mon-YY. My question is, can I still use a Select * query and be able to view all of the columns and the full date and time info, or is my only option to select each column and use the TO_CHAR for column C?
Yes. I mean, yes, you can do either. But don't.
Let me explain. You can use "select star" and view all of the columns, but then to obtain a reformatted date, you'd have to include an extra column containing the appropriate function.
select *, to_char(C,"YYYY-MM-DD HH24:MI") as C1 from datatable
However, this means that in whatever context you want to make reference to the formatted date, whether in a report or in an application language or script, you have to refer to column C1 instead of column C. You can't call it C, because there's already a column called C being selected. So in this approach, not only do you have to change your application code to use a different column name, you're also carrying extra data, because C "comes along for the ride," as it were. I do not advise this strategy. See Why "select star" is bad (9 March 2004).
Selecting each column is not your only option, but it is your best one.
select A, B, to_char(C,"YYYY-MM-DD HH24:MI") as C, D, ... from datatable
This method may seem tedious at first, but I assure you, it is worth the effort.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.