如何在数据库SQL函数的两个日期内选择日期 [英] How to select date within two dates from database SQL function

问题描述

我在两个日期start_date和End_date中选择数据，

日期之间的Start_date和End_date在数据库中不存在。

如何在两个日期内获取日期。



我的尝试：

从PA_TIME_SCHEDULING_table中选择状态，SELECT_DATE，其中
 SELECT_DATE介于'2018/04/17'和'2018/04/19'

解决方案

实现此目的的一种方法如下：

注意：以下只是演示如何实现，需要根据您的要求进行编辑

  DECLARE   @ dteStart   DATETIME  
  DECLARE   @ dteEnd   DATETIME  
 
  SET   @ dteStart  = '  20180501' 
  SET   @ dteEnd  = '  20180514' 
 
  CREATE   TABLE ＃ TempDates 
（
 [TheDate]  DATETIME  
）
 
  WHILE   @ dteStart < DATEADD（日， 1 ， @ dteEnd ）
  BEGIN  
  INSERT   INTO  #TempDates（[TheDate]） VALUES （ @ dteStart ）
  SET  < span class =code-sdkkeyword> @ dteStart  = DATEADD（日， 1 ， @ dteStart ）
  END  
 
  SELECT  *  FROM  #TempDates 
 
  DROP   TABLE  #TempDates 

亲切的问候

如果 SELECT_DATE 作为日期存储在 PA_TIME_SCHEDULING_table 尝试明确地将开始日期和结束日期转换为日期。你永远不应该信任隐式转换，因为它受数据库设置的影响，



例如

 选择状态，SELECT_DATE 
 来自 PA_TIME_SCHEDULING_table 其中 
 SELECT_DATE    convert （ date ，'  2018/04/17'， 111 ）和  convert （ date ，'  2018/04/19'， 111 <如果 SELECT_DATE 存储为 varchar ，然后你可以通过将其转换为wher中的日期来获得正确的结果e子句，但我建议不要这样做，而是将数据类型更改为 date 。

I am Selecting the Data within two date start_date and End_date,
Start_date and End_date in between dates are not Existing in database.
how to get date within two dates.

What I have tried:

select STATUS,SELECT_DATE from PA_TIME_SCHEDULING_table where 
              SELECT_DATE between '2018/04/17' and '2018/04/19'

解决方案

One method of achieving this is as follows;
NOTE: The following is just a demonstration of how it can be achieved and will need to be edited for your requirements

DECLARE @dteStart DATETIME
DECLARE @dteEnd DATETIME

SET @dteStart = '20180501'
SET @dteEnd = '20180514'

CREATE TABLE #TempDates
(
	[TheDate] DATETIME
)

WHILE @dteStart < DATEADD(day, 1, @dteEnd)
BEGIN
	INSERT INTO #TempDates([TheDate]) VALUES(@dteStart)
	SET @dteStart = DATEADD(day, 1, @dteStart)
END

SELECT * FROM #TempDates

DROP TABLE #TempDates

Kind Regards

If the SELECT_DATE is stored as a date in PA_TIME_SCHEDULING_table try explicitly converting the start and end dates to date. You should never trust on implicit conversion since it's affected by database settings,

For example

select STATUS,SELECT_DATE 
from PA_TIME_SCHEDULING_table where 
SELECT_DATE between convert(date, '2018/04/17', 111) and convert(date, '2018/04/19', 111)

If the SELECT_DATE is stored as a varchar, then you could achieve the correct result by converting it into a date in the where clause, but instead of doing that, I would recommend changing the data type into date.

这篇关于如何在数据库SQL函数的两个日期内选择日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！