将varchar转换为日期 [英] Converting varchar to date

问题描述

我的表中有一列是varchar。我用它以

格式存储日期，例如01/12/2013我想要做的是将列转换为DataType日期时间或日期。我是SQL新手，所以很难用它。存储的所有日期都不同。

Hi,
I have a column in my table that is varchar. I use it to store dates in the format
e.g. 01/12/2013 What I would like to do is convert the column to DataType datetime or date. I'm new to SQL so having hard time with it. All the dates stored are different.

推荐答案

试试这个.. :)

try this.. :)

select convert(datetime,columnName)  as [DateTime]
select convert(date,columnName)  as [Date]

--or
select cast(columnName as datetime)  as [DateTime]
select cast(columnName as date)  as [Date]

首先，为了实现您需要为日期使用正确的列类型而做得很好。它可以避免格式化问题并占用更少的空间。



正如您已经拥有表格一样，在转换该列时您有几个选项。



1.您可以完全放下桌子，重新创建并重新填充它



2.你可以额外增加一个然后使用解决方案1中的技术填充新列。例如

Firstly, well done for realising that you need to use the correct column type for dates. It avoids formatting problems and takes up less space.

As you already have your table you have a few options when it comes to converting that column.

1. You could drop the table entirely, recreate it and re-populate it

2. You could add an extra column of the correct type then use the technique from solution 1 to populate the new column. E.g.

UPDATE tablename SET newColumn = CONVERT(date, oldColumn,103)

（您可能希望之后删除旧列）。您可以使用ALTER TABLE或通过Management Studio执行此操作



3.您可以根据旧表的内容创建一个新表格，例如

(You might want to delete the old column afterwards). You can do this with ALTER TABLE or via Management Studio

3. You could create a new table from the contents of the old one e.g.

SELECT column1, column2, column3, CONVERT(date, oldColumn,103), .. etc
INTO NewTable FROM OldTable

（NB你必须列出其他列）然后删除旧表

如果是我，我可能会选择1，除非你真的有很多数据你不能轻易复制

(NB you must list the other columns) and then drop the old table
If it was me I would probably go with option 1 unless you really have lots of data you can't easily reproduce

这篇关于将varchar转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！