Comment to your article "Select star" with column reformatting. If you are using Oracle, the obvious solution is to enter:
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
Then all date columns will be displayed in this format.
Requires Free Membership to View
We received a ton of responses like the above. Apparently, this solution was obvious to many people, but not me. <sigh />
I guess part of the problem is that I don't work with Oracle on a day-to-day basis. I don't even have access to an Oracle system to test my queries. And before we get additional responses telling me to download some 800-megabyte install file, let me just say that for the time being I am not prepared to do that, sorry.
So that leaves me two choices. I could simply choose not to answer any questions that deal with Oracle. In the original question, the use of the TO_CHAR function was an obvious giveaway. So scenarios in which I try to apply good old ordinary cross-platform basic SQL, for which there happens to be a spiffier Oracle solution, will never see the light of day.
Alternatively, I could do my best and just take my lumps when there's a much more elegant solution that's obvious to you Oracle folks but not to me.
Which do you think is the best strategy?
By the way, several good comments and follow-up questions were also submitted. One person asked "I'm wondering about the AS part of the SELECT statement. Can C be used as a column alias as well as a column name?" This was in reference to the snippet:
to_char(C,"YYYY-MM-DD HH24:MI") as C
To tell you the truth, I don't know. Some databases will let you, some won't. As I said, I don't have Oracle to test on.
Another person commented: "Be aware that [alter session set nls_date_format] is a DDL command that issues a commit."
Finally, one person commented on the "select star" aspect of the question as follows:
In an application the best option is to list the column names. However, it initially sounded to me like the person asking the question was typing the command, maybe in SQL*Plus. In that case, "select *" is often used because typing even a subset of the table column names can be TOO tedious.
I totally agree with that, and I use "select star" when writing quick and dirty queries all the time, too.
So thanks for all the responses, folks. It's quite heartwarming to realize that there are many people out there reading this column. Let's hope I don't mess up too often, eh.
This was first published in February 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation