You must Sign In to post a response.
  • Category: SQL Server

    From using table between two dates get the in between dates

    select * from plandate

    fromdate todate
    2017-03-02 2017-03-09


    from the above table i want the ouput as follows

    2017-03-03
    2017-03-04
    2017-03-05
    2017-03-06
    2017-03-07
    2017-03-08


    for that how to write the query in sql server
  • #769068
    Hi,

    Please check the below code and let us know if it works,



    DECLARE @Date1 DATE, @Date2 DATE
    SELECT @Date1 = CONVERT(VARCHAR(10),fromdate,112) From Plandate
    SELECT @Date2 = CONVERT(VARCHAR(10),fromdate,112) From Plandate

    SELECT DATEADD(DAY,number+1,@Date1) [Date]
    FROM master..spt_values
    WHERE type = 'P'
    AND DATEADD(DAY,number+1,@Date1) < @Date2


    Thanks,
    Mani

  • #769097
    WITH date_range (range_date) AS (
    SELECT DATEADD(DAY, 0, '2017-03-02')
    UNION ALL SELECT DATEADD(DAY, 1, range_date)
    FROM date_range
    WHERE DATEADD(DAY, 1, range_date) <= '2017-03-09')
    SELECT range_date
    FROM date_range;

  • #769098
    try out with following procedure, it will help you more in order to resolve this

    create procedure [dbo].[p_display_dates](@startdate datetime,@enddate datetime)
    as
    begin
    declare @mxdate datetime
    declare @indate datetime
    create table #daterange (dater datetime)
    insert into #daterange values (@startdate)
    set @mxdate = (select MAX(dater) from #daterange)
    while @mxdate < @enddate
    begin
    set @indate = dateadd(day,1,@mxdate)
    insert into #daterange values (@indate)
    set @mxdate = (select MAX(dater) from #daterange)
    end
    select * from #daterange
    end

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #769122
    You can use given SQL Query for find table data between two dates
     select Date, TotalAllowance from Calculation where EmployeeId = 1
    and Date >= '2017/02/25' and Date <= '2017/02/27'

    Or
    SELECT *
    FROM Product_sales
    WHERE (
    From_date >= '2017-03-19'
    AND To_date <= '2017-03-23'
    )
    OR (
    To_date >= '2017-03-19'
    AND From_date <= '2017-03-23'
    )

    More useful reference : http://stackoverflow.com/questions/14208958/select-data-from-date-range-between-two-dates
    http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates

  • #769378
    SELECT * FROM tablename
    WHERE From_date between '2017-03-02'
    AND '2017-03-09 '

  • #769380
    You can achieve in simple select query. See below :

    Assuming your data type of the date column in the table is "Varchar".

    Select [datecolumn] from plantable where convert(smalldatetime,[datecolumn],105) between convert(smalldatetime,'2017-03-02',105) and convert(smalldatetime,'2017-03-09',105) order by convert(smalldatetime,[datecolumn],105)

    If it helps you then Rate this.

    Best Regards,
    Manoranjan Sahoo
    http://www.dotnetsquare.com
    Blog: http://blog.msahoo.net


  • Sign In to post your comments