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

问题描述

我在SQL表的列中有2016年2月20日星期六等数据，并想在查询中将其转换为日期或日期时间值。









我需要将它作为日期或日期时间用于比较查询。



需要任何帮助/指南。



我尝试过：



搜索谷歌一段时间后，我仍然无法转换或将其转换为我所需的格式。

I have a data like 'Sat, February 20, 2016' in columns of my SQL Table and wanted to convert it to date or datetime value in a query.




I need it as date or datetime to use in the comparative query.

Any help/guide is required.

What I have tried:

After searching google for a bit of time, I am still unable to convert or cast it to my required format.

推荐答案

几乎，你不能 - 或者至少，不是简单的。

问题是SQL没有包含星期几的日期格式样式： SQL Server CONVERT（）函数 [ ^ ]所以你需要先做一些严重的字符串操作。

如果你剥离一周中的某一天，这很简单：

Pretty much, you can't - or at least, not simply.
The problem is that SQL doesn't have a date format style that includes the day of the week: SQL Server CONVERT() Function[^] so you need to do some farily serious string manipulations first.
If you strip off the day of the week, it's fairly simple:

CONVERT(DATE, SUBSTRING('Sat, February 20, 2016', 6 , 999), 109)

会做什么它。

但是......必须这样做通常表明你的数据库设计存在严重缺陷：你根本不应该以字符串格式存储日期，因为你总是遇到这样的问题。我强烈建议真正的解决方案是编写一个快速的应用程序，将整个数据库转换为使用DATETIME列而不是NVARCHAR - 它将在未来为您节省很多麻烦。

Will do it.
But...having to do this is normally a sign that your DB design is badly flawed: you should not be storing dates in string format at all because you always get problems like this. I'd strongly suggest that the real solution is to write a quick app that converts your whole DB to use a DATETIME column instead of NVARCHAR - it'll save you a lot of grief in the future.

您可以执行以下操作 -

You can do something like following-

DECLARE @MyVar AS VARCHAR(50)
SET @MyVar='Sat, February 20, 2016'

SELECT RIGHT(@MyVar,LEN(@MyVar)-5)
SELECT CAST(RIGHT(@MyVar,LEN(@MyVar)-5) AS DATETIME)

希望，它有帮助：）

Hope, it helps :)

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