我想在sql server中将varchar列转换为日期格式？怎么转换？ [英] i want to convert varchar columns to date format in sql server? how to convert?

问题描述

我想在sql server中将varchar列转换为日期格式？怎么转换？



你能帮助我吗？提前祝愿。

Hi,

i want to convert varchar columns to date format in sql server? how to convert?

can you help me anyone. Advance wishes.

推荐答案

取决于你的意思。

如果你想选择一个VARCHAR列，并将其作为DateTime值返回给一个外部应用程序，然后这很简单 - 但如果数据不完全符合您的想法，则容易出现异常：

Depends what you mean.
If you want to select a VARCHAR column, and return it as a DateTime value to an outside application, then that's simple - but prone to exceptions if the data is not exactly what you think it should be:

SELECT CONVERT(datetime,myColumn) AS [Date]

如果你的意思是你有一个带有VARCHAR列的现有表，并且您希望将其转换为DATETIME列，那么您必须做一些工作：使用与旧列相同的列创建一个新表，除非在相应的列中使用DATETIME而不是VARCHAR。 br />
然后，将旧表中的每一行复制到新表中，转换上面的日期。

然后你可以删除旧表，并重命名ne w。

如果您尝试在SSMS中或通过查询更改列类型，您将删除所有现有的VARCHAR数据，它将不会被转换。

If you mean you have an existing table with a VARCHAR column and you want to convert it to a DATETIME column, then you have to do some work: create a new table with the same columns as the old, except using DATETIME instead of VARCHAR in the appropriate column.
Then, copy each row from the old table to the new table, converting the dates as above.
You can then delete the old table, and rename the new.
If you try to ALTER the column type in SSMS or via a query, you will delete all the existing VARCHAR data, it will not be converted.

Alter table tbl1
Add  datecol Datetime


UPDATE tbl1
set datecol = strcol

Alter table tbl1
DROP  strcol 

这篇关于我想在sql server中将varchar列转换为日期格式？怎么转换？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！