9

I was wondering. If I have a table with 20(or more) column names in and I want to select All of them, but one column of those 20 columns is a date column and you would like to change the format of that column, how would you do that? This obviously didn't work (created duplicate columns)

Select *, CONVERT(varchar(100),courseStartDate,111) from EthicsManagement

This is to avoid writing a select statement selecting ALL 20 columns individually and converting one of them with the statement 

  Select xxxx,xxx,xxx,xxx,xx,xx,xxx,xxx,xx,xx,xxx,xxx,xx, CONVERT(varchar(100),courseStartDate,111) from xxx
Charles
  • 50,943
  • 13
  • 104
  • 142
Ruan
  • 3,969
  • 10
  • 60
  • 87
  • Possible duplicate http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea – bvr Apr 15 '13 at 12:43

3 Answers3

9

It should let you do that, as long as you name the column something different:

Select *, CONVERT(varchar(100),courseStartDate,111) as myConvertedDateColumn
from EthicsManagement
Aaron
  • 55,518
  • 11
  • 116
  • 132
  • But still wont it have a duplicate column? a Column with the wrong format and one with the right format. (because we first select *) – Ruan Apr 15 '13 at 12:33
  • 6
    Correct. And that what sucks about doing a SELECT * – Aaron Apr 15 '13 at 12:34
  • 1
    Well that sucks... So you cant just change that one column format without selecting EACH and every column individually even though you want to select * in that table. – Ruan Apr 15 '13 at 12:35
0
Select t.*, CONVERT(varchar(100),courseStartDate,111) as converted from EthicsManagement t
Robert
  • 19,800
  • 5
  • 55
  • 85
  • 3
    You should add some explanations of what you did different, than OP and why in order to provide a more complete answer. – Hugo Dozois Apr 15 '13 at 12:52
0

I see two options:

  1. Use the SCRIPT AS TABLE menu option to have it automatically type out all column names and then remove the one you don't want (and add your CONVERT() function there) - See this StackOverflow answer;

  2. Create a TEMP table where you DROP COLUMN the duplicate column - See this StackOverflow answer.

sabacherli
  • 180
  • 1
  • 6