Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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);
Posted
Updated 1-Aug-17 13:15pm
Comments
Andy Lanng 31-Jul-17 12:02pm    
Looks fine to me. No error
Maideen Abdul Kader 31-Jul-17 20:45pm    
Thanks

I ended up with error :
Msg 206, Level 16, State 2, Procedure usp_OP_Leave_Date, Line 13
Operand type clash: date is incompatible with int

Pls advice me.
PIEBALDconsult 31-Jul-17 12:34pm    
And?
Maideen Abdul Kader 31-Jul-17 20:47pm    
ended with error

I ended up with error :
Msg 206, Level 16, State 2, Procedure usp_OP_Leave_Date, Line 13
Operand type clash: date is incompatible with int

PIEBALDconsult 31-Jul-17 21:00pm    
Datefrom + NOAUX

Try 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:


SQL
;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);
 
Share this answer
 
Thanks all

Advice from PIEBALDconsult

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

SQL
;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);


Thank you

maideen
 
Share this answer
 
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) 
 
Share this answer
 
Hi Try following stored procedure. using this procedure am finding data between postdate-From to Postdate-To

ALTER PROCEDURE [dbo].[Fetch_SellerInfo]


@PostdateFrom datetime=null,
@Postdateto datetime=null,

AS
BEGIN


select
Property.PropertyDetailsId,PostDate

From
PropertyDetails


where

(@PostdateFrom IS NULL and @Postdateto IS NULL OR PostDate between
@PostdateFrom and @Postdateto )
AND
property.Delflag=0
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900