如何检索两个日期之间的日期？ [英] How to retrieve dates between two date?

问题描述

您好b $ b

对不起，我不知道如何撰写问题/主题以满足以下要求，也不懂英语..



我有一个问题。

我有两个日期，如2017-07-29至2017-08-02 = 5天
两列中的
表中的（dateFrom和DateTo）。



我需要使用asp.net或存储过程从上一个表中编写另一个这样的表。我正在使用MSSQL 2014 + ASP.Net



2017-07-29

2017-07-30

2017-07-31

2017-08-01

2017-08-02



请咨询我该怎么做



提前感谢您



Maideen



我尝试过：

Hi
Sorry, I don't know how to write question/subject for the following request and also poor English..

I have an issue that is.
I have two dates like 2017-07-29 to 2017-08-02 = 5 days
in two columns (dateFrom and DateTo) in table.

I need to write another table like this from previous table using asp.net or stored procedure. I am using MSSQL 2014 + ASP.Net

2017-07-29
2017-07-30
2017-07-31
2017-08-01
2017-08-02

Pls advice me how to do this

Thanking you in advance

Maideen

What I have tried:

;with CTE_No as
(
    select 0 as NoAux
	union all
	select NoAux + 1 from CTE_No where NoAux < 300
)

INSERT INTO [dbo].[OP_Leave_Date]([Code],[Name],[Datefrom])

SELECT [Code],[Name],[Datefrom] + NOAUX
FROM [dbo].[OP_LeaveApplication] CROSS JOIN CTE_No
WHERE Datefrom + NOAUX <= Dateto 
OPTION (MAXRECURSION 0);

推荐答案

我试过有和没有dateadd这有两种方式。问题可能在你所使用的sql server版本中。这没关系，只需使用如下所示的dateadd：

I tried this with and without dateadd and it works both ways. The problem may in the the version of sql server you're on. It doesn't matter tho, just use dateadd as below:

;with CTE_No as
(
    select 0 as NoAux
	union all
	select NoAux + 1 from CTE_No where NoAux < 300
) , 
--test data
[OP_LeaveApplication] as (
SELECT 1 [Code],'one' [Name], cast('29 jul 2017' as datetime) [Datefrom],  cast('02 aug 2017' as datetime) [Dateto]
) 
 
 
SELECT [Code],[Name],dateadd(day,noaux, [Datefrom])
FROM [OP_LeaveApplication] CROSS JOIN CTE_No
WHERE dateadd(day,noaux, [Datefrom]) <= Dateto 
OPTION (MAXRECURSION 0);

全部谢谢



来自PIEBALDconsult的建议



我使用了DATEADD。现在工作正常。这是代码

Thanks all

Advice from PIEBALDconsult

I used DATEADD. Now is working fine. This is the code

;with CTE_No as
(
    select 0 as NoAux
	union all
	select NoAux + 1 from CTE_No where NoAux < 300
)

INSERT INTO [dbo].[OP_Leave_Date]([Code],[Name],[Datefrom],dateto,nodays,id,typeleave)

SELECT [Code],[Name],dateadd(d,NOAUX,[Datefrom]),dateto,nodays,id,typeleave  
FROM [dbo].[OP_LeaveApplication] CROSS JOIN CTE_No
WHERE dateadd(d,NOAUX,Datefrom)  <= dateto 
OPTION (MAXRECURSION 0);

谢谢



maideen

Thank you

maideen

Hi try this
<pre>insert into Table1(todate, Catname)
SELECT        CatName, Dateadded
FROM                          TblCat where dateadded between convert(datetime, '6/7/2017 ', 101)   and convert(datetime, '6/8/2017  ', 101) 

这篇关于如何检索两个日期之间的日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！