SQL - Cast fields to data types

Hello Experts,

I need to convert fields in a query to particular datatypes.  But there's a lemon twist here ... the data types are part of the actual data.  That's confusing, so let me show you an example of the query results.

Data_Field      |      Data_Type
-------------------------------------------------------------
City                          varchar(25)
State                        varhcar(2)
Zip                           numeric
Date                        datetime

One field has the data, the other field has the expected data type.
So in my query, I need to do something like this.

SELECT CAST(Data_Field as Data_Type) as 'Data_Field'

But SQL doesn't like this.  Can anyone help me with syntax to make this work?

Many thanks,
Moe
LVL 1
mauricerichardAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CluskittCommented:
SELECT CAST([Date] as datetime) AS 'Date'
You should always avoid naming your fields/tables the same as SQL functions/special words, though
0
CluskittCommented:
You can also use MyTableName.MyFieldName nomenclature to avoid these situations. However, this won't avoid other issues. One I've seen recently was some here on EE that had a table with a field named Day. He then had a syntax error trying to perform a DateAdd(day,...,...) because SQL interpreted day as the field and not as the DateAdd argument.
0
lluddenCommented:
You need to use dynamic SQL.

DECLARE @Sql VARCHAR(MAX)
DECLARE @T1 TABLE (Data_field VARCHAR(20), Data_Type VARCHAR(20))
INSERT INTO @T1 SELECT 'City','varchar(25)' UNION SELECT 'State','varchar(2)' UNION SELECT 'Zip','Numeric' UNION SELECT 'Date','datetime'
SELECT @SQL =
  COALESCE(
    @SQL + ',Cast([' + t1.Data_Field + '] AS ' + t1.Data_type + ') AS [' + t1.Data_Field + ']',
    'SELECT Cast([' + t1.Data_Field + '] AS ' + t1.Data_type + ') AS [' + t1.Data_Field + ']'
  )
FROM @T1 T1
SET @Sql = @Sql + ' FROM MyTable'

EXECUTE (@Sql)
--Print @SQL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

mauricerichardAuthor Commented:
Hi Cluskitt,

Sorry, this is very hard to explain.  Point taken with the naming conventions, but lets not worry about that for now.  Let me try to re-explain this.

The table has 2 fields: 1) Data_Field  2) Data_Type

Imagine you just created a table called Addresses with two fields, Data_Field & Data_Type

Now imagine you ran this statement for this table:
INSERT INTO Addresses(Data_Field, Data_Type) VALUES('City', 'varchar(25)')
INSERT INTO Addresses(Data_Field, Data_Type) VALUES('State', 'varchar(2)')
INSERT INTO Addresses(Data_Field, Data_Type) VALUES('Zipcode', 'numeric')
INSERT INTO Addresses(Data_Field, Data_Type) VALUES('Date', 'datetime')

You'd now have data that looked like I explained in my question.  But it's just data, the data_Type field isn't a SQL datatype ... it's a string of data.

So what I'd like do to is convert the Data_field to whatever Data_Type we entered for it.  So in the example above, when 'City' loads, I want it to be converted to varchar(25) ... when 'Date' loads up I want to convert it to datetime.

So my first thought was: SELECT CAST(Data_Field as Data_Type) as 'Data_Field'
But I get the message: Type c.Data_Type is not a defined system type.
0
CluskittCommented:
I understand now. You have to use dynamic SQL as lludden posted, or some similar variations (like using Quotename functions). The way I handled a similar situation was to create a stored procedure that had @Field and @Type as arguments. You then create your query much like the example posted above (if you use Quotename, it surrounds the string with [])
0
mauricerichardAuthor Commented:
Great, thanks for all the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.