如何在数据库SQL函数的两个日期内选择日期 [英] How to select date within two dates from database SQL function
本文介绍了如何在数据库SQL函数的两个日期内选择日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在两个日期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 theSELECT_DATE
is stored as a date inPA_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 theSELECT_DATE
is stored as avarchar
, 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 intodate
.
这篇关于如何在数据库SQL函数的两个日期内选择日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文