如何在SQL Server中投射时间 [英] How to cast time in SQL server

问题描述

如何施放时间以及如何在sql server中进行比较

我正在使用以下



我收到此错误



消息241，级别16，状态1，行1

从字符串转换日期和/或时间时转换失败。





谢谢



我的尝试：

how to cast time and how to compare in sql server
I am using following

I getting this error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


Thanks

What I have tried:

update WorkingHours set intime='09:45' where cast(intime as time) >= '09:46' and cast(intime as time) <= '09:55'
  PRINT ''
  update WorkingHours set outtime='18:15' where cast(outtime as time) >= '18:16'
  update WorkingHours set intime='09:25' where cast(intime as time) < '09:20'
  update WorkingHours set LateComing='Yes' where cast(intime as time) > '10:45' and InTime is not null
  update WorkingHours set EarlyLeaving='Yes' where cast(OutTime as time) < '05:45' and OutTime is not null
  update WorkingHours set MissPunchDate=date where InTime is not null and OutTime is null
  update WorkingHours set MissPunchDate=date where OutTime is not null and InTime is null
  
  update WorkingHours set ShortPerDay='Yes' where (dbo.[MinutesToDuration](datediff(minute,intime,outtime))) < '8' and InTime is not null and OutTime is not null

推荐答案

您需要比较具有数值的项目。

int，float，时间，日期，日期时间等都有数值。

字符串，另一方面，没有。



如果你希望比较两次然后他们必须都被施展到TIME。

改述：

你无法比较演员（时间和时间）和'18：16'
执行此操作：

You need to compare like items that have numerical values.
int, float, time, date, datetime, etc., all have numerical values.
Strings, on the other hand, do not.

If you wish to compare two times then they must both be cast to TIME.
Rephrasing:
You cannot compare cast(outtime as time) and '18:16'
Do this:

cast(outtime as time) > cast('18:16' as time) 

这是一个时间函数的良好概述：如何从Sql Server中的DateTime获取时间，小时，分钟，秒和毫秒部分SqlHints.com [ ^ ]

另见： SQL Server 2008 - 时间差（0） - 堆栈溢出 [ ^ ]

Here is a good overview of Time functions: How to get Time, Hour, Minute, Second and Millisecond Part from DateTime in Sql Server | SqlHints.com[^]
Also see: SQL Server 2008 - Difference between time(0) - Stack Overflow[^]

这篇关于如何在SQL Server中投射时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！