Is it possible to change the datatype of a column in a view?

Question

Usually I run a script like this:

ALTER TABLE [TABLE]
ALTER COLUMN [Column] NVARCHAR(40);

The result is that the field in the table gets converted to nvarchar. But what is the syntax for doing the same thing for a view? Or is that even possible?

You can't dictate the data type for the output column of a view. The best you can do is as @buckley suggested - use CAST or CONVERT to force an explicit data type inline in the view definition. — Aaron Bertrand, May 22 '12 at 23:44

buckley · Accepted Answer · 2012-05-22T23:31:33.310

52

Sure

CREATE VIEW AView
AS
SELECT CAST(title AS char(50))
FROM titles

So check out CAST and also CONVERT on the msdn pages for full info

edited May 22 '12 at 23:31

answered May 22 '12 at 23:24

buckley

13,690
3
53
61

score 3 · Answer 2 · answered Sep 30 '21 at 13:27

3

If you have updated the type in the table and the view still reflects the old type, simply running an alter view without changing anything will update the type

answered Sep 30 '21 at 13:27

jimmy

31
1

1

Thank you for contributing, but I don't think this answer adds anything new or useful to the discussion. – Philip Wrage Sep 30 '21 at 21:31
1

I respectfully disagree, why not let the original asker decide – jimmy Nov 19 '21 at 14:26

score 0 · Answer 3 · answered Mar 02 '16 at 04:52

Yes..You can try Convert function to do this.

Convert (Desired datatype,column name)

eg.Convert(varchar(50),dbo.User_master.User_email) where User_email has previous type as nvarchar(MAX).

If you want to convert nvarchar data to datetime then additional parameter is needed to Convert function like

CONVERT(data_type(length),expression,style)

eg. Convert(Datetime,dbo.User_master.User_DOB,103)

more info at SQL Server CONVERT() Function

Is it possible to change the datatype of a column in a view?

3 Answers3

Linked