Alter Data type in a view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Alter Data type in a view

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2007-10-19 : 12:29:19

Hi,
I have been asked to make a view of a tbale Tran1.
In here is a field EvDate that they have in the Table as Varchar(8).
In the view they want me to make this a Datatypew datetime. But I do not see a way to do this.
Can someone help me.
Thanks

ITM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 12:30:54

You can use the CONVERT or CAST functions to change data types.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2007-10-19 : 13:15:06

Yah I tried several of these nothing I have done worked.
Here is the latest
Select EVTDATE = CONVERT(varchar(50), EVTDATE, 111) --AS DateTime
FROM dbo.Tranaction

ITM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 13:17:07

What do you mean it hasn't worked? Do you get an error, unexpected results, ...?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-19 : 13:22:13

quote:
Originally posted by itmasterw

Yah I tried several of these nothing I have done worked.
Here is the latest
Select EVTDATE = CONVERT(varchar(50), EVTDATE, 111) --AS DateTime
FROM dbo.Tranaction

ITM

What you have above would convert a datetime to a varchar, what you are trying to do is convert a varchar to a datetime so you need to change it to:
SELECT evtdate = CONVERT(DATETIME, EVTDATE)
FROM dbo.Transaction

Future guru in the making.

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2007-10-24 : 18:42:42

Thank you I got it to work thanks for your help

ITM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-25 : 02:35:57

quote:
Originally posted by Zoroaster

quote:
Originally posted by itmasterw

Yah I tried several of these nothing I have done worked.
Here is the latest
Select EVTDATE = CONVERT(varchar(50), EVTDATE, 111) --AS DateTime
FROM dbo.Tranaction

ITM

What you have above would convert a datetime to a varchar, what you are trying to do is convert a varchar to a datetime so you need to change it to:
SELECT evtdate = CONVERT(DATETIME, EVTDATE)
FROM dbo.Transaction

Future guru in the making.

Also make sure the column has valid date values

WHERE ISDATE(col)=1 and LEN(col)=8

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources