asked on

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

Cluskitt

SELECT CAST([Date] as datetime) AS 'Date'
You should always avoid naming your fields/tables the same as SQL functions/special words, though

Cluskitt

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.

ASKER CERTIFIED SOLUTION

lludden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mauricerichard

ASKER

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.

SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mauricerichard

ASKER

Great, thanks for all the help!