得到两个日期或两个小时之间的差，然后插入它们，否则不 [英] Get the difference between 2 dates or 2 hours and then insert them else not

问题描述

我的表Events中有2个字段(Game_Date，Hour_Game).这些字段以Varchar(50)作为数据类型，我不想将它们的Datetime或time作为数据类型传递给我，我想将它们保留为Varchar(50) )，然后我要计算输入的日期与表中已经存在的日期之间的差值(如果插入的日期大于1天，则应插入该日期，否则不插入);如果小时数大于2，则与小时相同在执行SP时未插入，即使日期大于1或小时大于2小时也使我进入了else条件，这是我正在使用的SP， :

Hi I have 2 fields(Game_Date,Hour_Game) in my table Events.Those field has Varchar(50) as Datatype and I dont want to pass them Datetime or time as data type.I want to keep them as Varchar(50),Then I want to take the difference between the date entered and the date that already exists in the table if the date inserted is greater than 1 day the date should be inserted else not,same for the hour if the hour is greater than 2 hours it should be inserted else not.When executing the SP it is not inserted,It took me into the else condition even if the date is greater than 1 or the hour is greater than 2 hours, this is the SP I am using and It :

ALTER PROC [dbo].[InsertData]
@Ev VARCHAR(50)='',
@idstadium VARCHAR(50)='',
@GameDate VARCHAR(50)='',
@GameHour VARCHAR(50)='',
@msg VARCHAR(4000)=NULL OUTPUT
AS
BEGIN
DECLARE @i AS INT
DECLARE @DateDiff AS VARCHAR(50)
DECLARE @DateHour AS VARCHAR(50)
 
 
SET @DateDiff=(SELECT DATEDIFF(day,Game_Date,@GameDate) FROM dbo.Events WHERE EventsID=@Ev) 
SET @DateHour=(SELECT DATEDIFF(hour,Hour_Game,@GameHour) FROM dbo.Events WHERE EventsID=@Ev)
 
          IF (@DateDiff>1 and @DateHour>2)
          BEGIN
          INSERT INTO dbo.Events(EventsID,Id_Stadium,Game_Date,Hour_Game)
          VALUES(@Ev,@idstadium,@GameDate,@GameHour)
          SET @msg='Data inserted!'
          END
          ELSE
          BEGIN
           SET @msg='Hour should be greater than 2 or Date should be greater than 1'
         END
      
END

帮助解决此问题...

Help fixing this...

推荐答案

如果您想进行任何数学运算日期或时间上的表格，那么唯一明智的选择是将其存储为DateTime.

否则，正如您所发现的，它将成为可以使用的总PITA.

硬着头皮.更改您的数据库数据类型.从长远来看，它将为您节省很多麻烦.

抱歉，如果这不是您想要的答案-但这确实是您可以获得的最佳答案.

If you want to do math of any form on a date or time, then the only sensible option is to store it as DateTime.

Otherwise, as you have found, it becomes a total PITA to work with.

Bite the bullet. Change your database datatype. It will save you a heck of a lot of hassle in the long run.

Sorry if that is not the answer you wanted - but it really is the best answer you can get.

您必须花很长时间研究这个问题.在您的消息(其他子句)中，使用单词或"，但是如果您仔细查看如果"条件，则肯定会注意到和"运算符.我确定您会发现它确实应该是或".

问候，

曼弗雷德(Manfred)

You must be looking at this for too long. In your message (else clause) you use the word "or", but if you look hard at the "if" condition you''ll surely notice the "and" operator. I''m certain you''ll figure out that it should really be an "or".

Regards,

Manfred

这篇关于得到两个日期或两个小时之间的差，然后插入它们，否则不的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！