locked
Retrieve the Value between 2 dates RRS feed

  • Question

  • Hi

    Suppose that i have a table called Reco and have Recfrom and recto

    and these two fields = 11/1/2009 To 11/15/2009

    is there any statement that select all the days between then them
    i mean i want results as below

    11/1/2009
    11/2/2009
    11/3/2009
    ........ Till 11/15/2009
    Wednesday, December 16, 2009 11:56 AM

Answers

  • Maged,

    Check out the following view CREATE and SELECT demo. Let us know if helpful.

    -- T-SQL SELECT from VIEW demo
    USE AdventureWorks2008;
    GO
    CREATE VIEW vSales
    AS
    SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + 
                   p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal] AS Sales
            ,YEAR(soh.[OrderDate]) AS [FiscalYear],
            OrderDate,
            SalesOrderID
        FROM [Sales].[SalesPerson] sp 
            INNER JOIN [Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    GO
    
    SELECT FullName, Sales, SalesOrderID
    FROM vSales
    WHERE  OrderDate >= '2002-01-01' and OrderDate < '2003-01-01'
        AND FullName = 'Linda C Mitchell'
     ORDER BY SalesOrderID
     /* 
     FullName	Sales	SalesOrderID
    Linda C Mitchell	503.3507	45040
    Linda C Mitchell	6080.0092	45044
    Linda C Mitchell	503.3507	45073
    Linda C Mitchell	80385.1926	45274
    Linda C Mitchell	4616.2591	45286
    Linda C Mitchell	40261.8415	45290
    */
    	
    

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Tuesday, December 22, 2009 8:43 AM
    Wednesday, December 16, 2009 12:33 PM
  • Maged,

    This is how you have to use the time range filter:

    OrderDate >= '2002-01-01' and OrderDate < '2003-01-01'
       

    Notice that I only want 2002, yet I had to put the upper limit to one day into 2003.

    This is because datetime has a TIME component also.

    It may look strange, but this is how you have to do it to get the correct range.

    In Ramesh's example '2009-11-15 01:01:01.000' would not be included, only '2009-11-15 00:00:00.000'  would .

    Article: http://www.sqlusa.com/bestpractices2008/between-dates/


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

    • Marked as answer by Kalman Toth Thursday, May 10, 2012 7:00 AM
    • Edited by Kalman Toth Thursday, May 10, 2012 7:01 AM
    Wednesday, December 16, 2009 1:09 PM

All replies

  • Select * from DIFFDATES where Dates>='11/1/2009' and Dates <='11/15/2009'
    Wednesday, December 16, 2009 12:05 PM
  • i think you got me wrong

    i want to get the diff from two fields (recfrom and recto) and the table name is reco

    Wednesday, December 16, 2009 12:11 PM
  • create table #Reco (RecFrom datetime, RecTo datetime)
    insert into #Reco SELECT '1 nov 2009','15 nov 2009';
    
    with calendarCTE
    as
    (select RecFrom as dt, RecTo as endDate from #Reco
    union all
    select dateadd(d, 1, dt), endDate
    from calendarCTE
    where dt < enddate
    )
    select dt
    from calendarCTE OPTION (MAXRECURSION 0);

    every day is a school day
    Wednesday, December 16, 2009 12:11 PM
  • Declare 
    	@fromDate datetime,
    	@ToDate Datetime,
    	@DayDiff Int
    
    Set @fromDate='11/1/2009'
    Set @ToDate='11/15/2009'
    Set @DayDiff=Datediff(day,@fromDate,@ToDate)
    
    Select top(@DayDiff+1) dateadd(day,row_number() Over(order by (Select 1))-1,@fromDate) from sys.columns a cross join sys.columns b
    
    
    
    Wednesday, December 16, 2009 12:13 PM
  • sorry if i am beginner to understand this
    but for clarification

    i am using VB and SQL Server 2000 how can i create a view that gives me the result between two field thier type is Date Where Recname = "Michael"

    Wednesday, December 16, 2009 12:17 PM
  • Maged,

    Check out the following view CREATE and SELECT demo. Let us know if helpful.

    -- T-SQL SELECT from VIEW demo
    USE AdventureWorks2008;
    GO
    CREATE VIEW vSales
    AS
    SELECT 
            soh.[SalesPersonID]
            ,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + 
                   p.[LastName] AS [FullName]
            ,e.[JobTitle]
            ,st.[Name] AS [SalesTerritory]
            ,soh.[SubTotal] AS Sales
            ,YEAR(soh.[OrderDate]) AS [FiscalYear],
            OrderDate,
            SalesOrderID
        FROM [Sales].[SalesPerson] sp 
            INNER JOIN [Sales].[SalesOrderHeader] soh 
            ON sp.[BusinessEntityID] = soh.[SalesPersonID]
            INNER JOIN [Sales].[SalesTerritory] st 
            ON sp.[TerritoryID] = st.[TerritoryID] 
            INNER JOIN [HumanResources].[Employee] e 
            ON soh.[SalesPersonID] = e.[BusinessEntityID] 
    		INNER JOIN [Person].[Person] p
    		ON p.[BusinessEntityID] = sp.[BusinessEntityID]
    GO
    
    SELECT FullName, Sales, SalesOrderID
    FROM vSales
    WHERE  OrderDate >= '2002-01-01' and OrderDate < '2003-01-01'
        AND FullName = 'Linda C Mitchell'
     ORDER BY SalesOrderID
     /* 
     FullName	Sales	SalesOrderID
    Linda C Mitchell	503.3507	45040
    Linda C Mitchell	6080.0092	45044
    Linda C Mitchell	503.3507	45073
    Linda C Mitchell	80385.1926	45274
    Linda C Mitchell	4616.2591	45286
    Linda C Mitchell	40261.8415	45290
    */
    	
    

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Tuesday, December 22, 2009 8:43 AM
    Wednesday, December 16, 2009 12:33 PM
  • yes it is helpful
    but the first Thread for ramesh need to be clarified if you can
    because i need to get the diff between two dates where name = somthing
    that's all and i need to create this view from vb2008
    Wednesday, December 16, 2009 12:43 PM
  • Maged,

    This is how you have to use the time range filter:

    OrderDate >= '2002-01-01' and OrderDate < '2003-01-01'
       

    Notice that I only want 2002, yet I had to put the upper limit to one day into 2003.

    This is because datetime has a TIME component also.

    It may look strange, but this is how you have to do it to get the correct range.

    In Ramesh's example '2009-11-15 01:01:01.000' would not be included, only '2009-11-15 00:00:00.000'  would .

    Article: http://www.sqlusa.com/bestpractices2008/between-dates/


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

    • Marked as answer by Kalman Toth Thursday, May 10, 2012 7:00 AM
    • Edited by Kalman Toth Thursday, May 10, 2012 7:01 AM
    Wednesday, December 16, 2009 1:09 PM