将SQL Server表中的String转换为Datetime并在Select Query中使用 [英] Convert String in SQL server table to Datetime and use in Select Query
问题描述
我有一个表,其中日期时间以及以格式存储在Varchar字段中
DD-MMM-yyyy-HH:MM:SS
,这就像 20-Feb-2013-17:23:34
所以,现在我想在这个表上使用select查询,例如,
让表为Employees, JoinDate 为 Varchar
现在我的查询如下所示
SELECT EmployeeName,JoinDate FROM 员工 WHERE JoinDate BETWEEN ' 20-Jan-2013-00:00:00' AND ' 20-Feb-2013-23:59:59'
但是我的查询结果出乎意料的结果。
现在我的问题是如何让员工加入b etween ''20-Jan-2013-00:00:00'' AND ''20-Feb-2013-23:59:59''。
HI,
您必须将JoinDate数据类型更改为datetime--解决了问题,以及许多额外的好处。
您无法在varchar数据类型之间使用这样的..它检查varchar数据以便不接受结果是...
如果您喜欢这个解决方案,那么请投票并标记为解决方案。
谢谢
Asp.Net C#帮助代码 [ ^ ]
Hemant Singh
请注意,日期和时间之间的短划线可能会导致问题。
我建议像CAST(STUFF(JoinDate, 12 ,< span class =code-digit> 1 ,' ) AS DATETIME )
请注意,如果日期没有前导零,则12会发生变化。 01-FEB-2013
I have a table in which Date time is stored in Varchar field in the fallowing format
DD-MMM-yyyy-HH:MM:SS
, which is like 20-Feb-2013-17:23:34
So, now I wanted to use select query on this table for example,
let table be Employees and JoinDate is Varchar
Now my query look like below
SELECT EmployeeName,JoinDate FROM Employees WHERE JoinDate BETWEEN '20-Jan-2013-00:00:00' AND '20-Feb-2013-23:59:59'
But my query results unexpected results than what I expected.
Now my question is how to get the employees joined between ''20-Jan-2013-00:00:00'' AND ''20-Feb-2013-23:59:59''.
HI,
you have to change JoinDate datatype to datetime-- problem resolved, and many additional benefits.
YOU cant use between for varchar datatype like that..Its checking varchar data so that unaccepted results are there...
If you Like this solution, so please vote and mark as solution.
Thanks
Asp.Net C# Help Code[^]
Hemant Singh
Notice that the dash between the date and time may cause problems.
I suggest something likeCAST(STUFF(JoinDate,12,1,' ') AS DATETIME)
Notice that the 12 would change if the date does not have leading zero. 01-FEB-2013
这篇关于将SQL Server表中的String转换为Datetime并在Select Query中使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!