Sql server如果条件基于日期 [英] Sql server if condition based on date

问题描述

我需要帮助，给出if存在和是否存在条件。



我想写一个if条件，基于日期，这样，如果在特定作业的当前日期TARGET_DT中，table1中存在任何行，然后执行其他操作，执行其他操作。如下所示

  IF   EXISTS （ SELECT   1   FROM  COD_BLU_OPENING_VOLUME  WHERE  TARGET_DT = CONVERT（ DATETIME ，GETDATE（）， 101 ） AND  JOBID = @ OPENJOB）
  BEGIN  
  IF （ SELECT  COUNT（ 1  ） FROM  COD_BLU_INVENTORY  WITH （ NOLOCK ）< span class =code-keyword> WHERE （JOBID = @ JOB  AND  STATUS = '  A'  AND  TARGETDT =  CONVERT （ DATETIME ， @ TARGETDT ， 101 ）））= 0 
  BEGIN  
  INSERT   INTO  COD_BLU_INVENTORY（JOBID，TARGETDT，CARRYOVER，FRESHVOL，TOTALVOL，STATUS，UPDATEDBY，UPDATEDT，OPENINGVOL）
  VALUES （ @ JOB ， @ TARGETDT  ， @ CARRYOVERVOL ， @ FRESHVOL ， @ TOTALVOL ，'  A'， @ EMPCODE  ，GETDATE（）， @ CARRYOVERVOL ）
  SELECT  '  1' 
  END  
  ELSE  
  BEGIN  
  SELECT  '  0' 
  END  
  END  
  ELSE   IF   NOT   EXISTS （ SELECT   1   FROM  COD_BLU_OPENING_VOLUME  WHERE  TARGET_DT = CONVERT（ DATETIME ，GETDATE（）， 101 ） AND  TARGET _DT< CONVERT（ DATETIME ，GETDATE（）， 101 ） AND  JOBID = @ OPENJOB）
  BEGIN  
  INSERT   INTO  COD_BLU_OPENING_VOLUME（TARGET_DT，JOBID，OPENING_COUNT，UPDATED_BY，UPDATED_DT，PRE_ASSIGNEDCOUNT）
  VALUES （ @ OPENTARDT ， @ OPENJOB ， @ OPENCNT ， @ OPENEMPCODE ，GETDATE（）， @ PREOPENCNT ）
 
  IF （ SELECT  COUNT（ 1 ） FROM  COD_BLU_INVENTORY  WITH （ NOLOCK ） WHERE （JOBID = @ JOB  AND  STATUS = '  A'  AND  TARGETDT =  CONVERT （  DATETIME ， @ TARGETDT ， 101 ）） ）= 0 
  BEGIN  
  INSERT   INTO  COD_BLU_INVENTORY（JOBID，TARGETDT，CARRYOVER，FRESHVOL，TOTALVOL，STATUS，UPDATEDBY，UPDATEDT，OPENINGVOL）
  VALUES （ @ JOB ， @ TARGETDT ， @ CARRYOVERVOL ， @ FRESHVOL ， @ TOTALVOL ，'  A'， @ EMPCODE ，GETDATE（）， @ CARRYOVERVOL ） 
  SELECT  '  1' 
  END  
  ELSE  
  BEGIN  
  SELECT  '  0' 
  END  
  END  
 < span class =code-keyword> END  
 

< pre lang =text>但问题是，如果TARGET_DT作为将来的日期，它执行else条件，我希望它执行if条件，上面是我厌倦的代码。任何帮助解决这个问题将不胜感激。请帮忙。

我的尝试：



我试过上面给出的一个

解决方案

停止将日期转换为字符串来比较它们：当你你得到一个字符串比较，这意味着结果是基于两个字符串之间的第一个不同的字符。

您需要DATE或DATETIME比较：所以更改您的数据库以存储日期为DATE或DATETIME并直接将该值与GETDATE（）返回值进行比较。



始终以最合适的格式存储值，而不是字符串。它似乎更简单，但每次你想使用它时都会引起很大的问题。

I need help with, giving the if exists and if not exists condition.

I want to write an if condition, based on date, such that, if any row exists in table1 on current date TARGET_DT for a particular job, then do some action else do other action. like shown below

IF EXISTS(SELECT 1 FROM COD_BLU_OPENING_VOLUME WHERE TARGET_DT=CONVERT(DATETIME,GETDATE(),101) AND JOBID=@OPENJOB)
         BEGIN
             IF(SELECT COUNT(1) FROM COD_BLU_INVENTORY WITH(NOLOCK) WHERE (JOBID=@JOB AND STATUS='A' AND TARGETDT = CONVERT(DATETIME,@TARGETDT,101)))=0
                 BEGIN
                     INSERT INTO COD_BLU_INVENTORY(JOBID,TARGETDT,CARRYOVER,FRESHVOL,TOTALVOL,STATUS,UPDATEDBY,UPDATEDT,OPENINGVOL)
                     VALUES(@JOB,@TARGETDT,@CARRYOVERVOL,@FRESHVOL,@TOTALVOL,'A',@EMPCODE,GETDATE(),@CARRYOVERVOL)
                     SELECT '1'
                 END
             ELSE
                 BEGIN
                     SELECT'0'
                 END
             END
      ELSE IF NOT EXISTS(SELECT 1 FROM COD_BLU_OPENING_VOLUME WHERE TARGET_DT=CONVERT(DATETIME,GETDATE(),101) AND TARGET_DT<CONVERT(DATETIME,GETDATE(),101) AND JOBID=@OPENJOB)
         BEGIN
             INSERT INTO COD_BLU_OPENING_VOLUME(TARGET_DT,JOBID,OPENING_COUNT,UPDATED_BY,UPDATED_DT,PRE_ASSIGNEDCOUNT)
             VALUES(@OPENTARDT,@OPENJOB,@OPENCNT,@OPENEMPCODE,GETDATE(),@PREOPENCNT)

             IF (SELECT COUNT(1) FROM COD_BLU_INVENTORY WITH(NOLOCK) WHERE (JOBID=@JOB AND STATUS='A' AND TARGETDT = CONVERT(DATETIME,@TARGETDT,101)))=0
                 BEGIN
                     INSERT INTO COD_BLU_INVENTORY(JOBID,TARGETDT,CARRYOVER,FRESHVOL,TOTALVOL,STATUS,UPDATEDBY,UPDATEDT,OPENINGVOL)
                     VALUES(@JOB,@TARGETDT,@CARRYOVERVOL,@FRESHVOL,@TOTALVOL,'A',@EMPCODE,GETDATE(),@CARRYOVERVOL)
                     SELECT '1'
                 END
             ELSE
                 BEGIN
                     SELECT'0'
                 END
         END
     END

but the problem is,if the TARGET_DT is given as future date, it executes the else condition, I want it to execute the if condition, the above is the code which I tired. Any help in solving this will be appreciated. Please help.

What I have tried:

I have given a try like the one give above

解决方案

Stop converting dates to strings to compare them: when you do that you get a string comparison which means that the result is based on the first different character between the two strings.
You need a DATE or DATETIME comparison: so change your DB to store dates as DATE or DATETIME and compare that value against the GETDATE() return value directly.

Always store values in the most appropriate format, not as strings. It may seem to be simpler, but it causes huge problems every time you want to use them.

这篇关于Sql server如果条件基于日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！