CODEWITHSUNDEEP
sqlselectcasting
Ruan
Ruan

Reputation: 4283

SQL Select All and change the format of one column

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

Upvotes: 9

Views: 17712

Answers (3)

sabacherli
sabacherli

Reputation: 198

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.

Upvotes: -1

Aaron
Aaron

Reputation: 57748

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

Upvotes: 8

Robert
Robert

Reputation: 20286

Select t.*, CONVERT(varchar(100),courseStartDate,111) as converted from EthicsManagement t

Upvotes: -1

Related Questions