How to Change Datatypes In a view?

Question

How to Change Datatypes In a view?

NoviceJatt

SSCommitted

Points: 1671
More actions
July 28, 2010 at 3:32 pm

#221819

Hi
I want to change dataypes in a view.
My Table is:
CREATE TABLE DSA
( [CP Code] varchar(50),
[Time] varchar(50),
[Volume] varchar(50)
)
CP Code Time Volume
12345 7/22/2010 12345
12345 7/23/2010 12345
12345 7/24/2010 12345
12345 7/25/2010 12345
Excuse for the datatypes in my Table. They have to be as such. Can't change them before creating a view.
My View is:
CREATE VIEW View_DSA
AS
SELECT [CP Code],
[Time],
[Volume]
FROM DSA
CP Code [varchar to char]
Time [varchar to date]
Volume [varchar to int]
I Want to change the Datatypes after or during view creation.Whichever is better. How can I do that?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 1

Use the CAST fuction. If your datetime column is in a format different from the server default you can also use CONVERT and specify the format/style of the date string.

CREATE VIEW View_DSA

AS

SELECT

CAST([CP Code] AS CHAR(<length>) AS [CP Code],

CAST([Time] AS DATETIME) AS [Time],

CAST([Volume] AS INT) AS Volume

FROM DSA

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 2

its unfortunate that you can't change the table structure to be accurate, why do you need to change them?

however..

CREATE VIEW vDSA AS

SELECT

CAST([CP Code] AS CHAR(5)) AS [CP Code],

CAST([Time] AS DATETIME) AS [Time],

CAST([Volume] AS INT) AS [Volume]

FROM DSA

You might want to also look over: http://msdn.microsoft.com/en-us/library/ms187928.aspx

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

NoviceJatt SSCommitted Points: 1671 More actions · Answer 3

Hi

Thankyou very much guys.

@torpkev .... I'm importing data from unformated flat file. If I use correct datatypes in my Table, I'm not able to import data which is why I use VARCHAR and I cant change Datatypes in the Table coz I keep importing new data in my Table, so If I change the Datatypes, as I told you before, I'm not able to import new data from Flat file.

Thanks