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

    Missing Dates return as dataset

    Hi,

    I have 10 records Fromdate and Todate.

    Example: 01/01/2014 - 31/01/2014, 01/02/2014 - 31/02/2014, 01/03/2014 - 31/03/2014 .........................01/10/2014 - 31/10/2014.


    In this there are 2 months records are missing say months May and June i.e 01/05/2014 - 31/05/2014 and 01/06/2014 -31/06/2014 .

    I want to retrieve these missing 'From and To' months dates and return as Dataset using c#.


    Thanks.
  • #767672
    Hi

    First Check your Query then you verify your DS

    sample Query for Date Based Fetching Data



    select * from tblDate where Tdate between '01/01/2014' and '31/10/2014'
    select * from tblDate where Tdate between '2014-01-01' and '2014-10-31'
    select * from tblDate where Tdate >='2014-01-01' and Tdate <='2014-10-31'


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767681
    Hi,
    Please find below script:

    DECLARE @MinDate DATE = CAST('20140101' AS DATETIME),
    @MaxDate DATE = CAST('20141031' AS DATETIME);

    SELECT DateColumn
    FROM Table WHERE DateColumn>= @MinDate
    AND DateColumn< @MaxDate;

  • #767684
    Hi,

    If you want to retrieve your data between two dates then you have to make your query like below


    select * from tablename where datefield between @fromdate and @todate


    If the data present in the database then it will return data and display it your dataset, for show the result into dataset you have to made some code for the same.


    con.Open();
    cmd=new SqlCommand("select * from tablename where datefield between @fromdate and @todate", con);
    da=new SqlDataAdapter(cmd);
    ds= new DataSet();
    da.Fill(ds);


    This is the way you have to get data from database to your dataset.

    Hope this helps you..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #767688
    Hai Ayesha,
    You can use the simple select statement which will have either between or >= and <= as the conditions between the FromDate and ToDate.
    And if you have the date range and you want to get which month data is missing, then you need to use CTE(Common Table Expressions) where you will have the pass the end date and then it will find-out the months where the records are missing.
    Let's say in the year 2015, you want to get which month data is missing then you can use the below query:

    declare @startDate DATE = '2015-01-01'
    declare @endDate DATE = '2015-12-31'
    ;withMissingDates (date)
    as
    (
    select @startDate
    unionall
    select dateadd(month, 1, date)
    from MissingDates
    where dateadd(month, 1, date)<=@endDate
    )
    select yeaar(date) as Year, month(date) as Month
    from MissingDates

    except

    select distinct year(MissingDates) as year, month(MissingDates) as month
    from TableName
    where OrderId = 1 and MissingDates between @startDate and @endDate

    This will give you the result as Year and Month in which the data was not present.
    You can also follow the below link:

    http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/
    http://stackoverflow.com/questions/28138536/sql-server-select-missing-months-between-2-dates-in-same-year

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #767713
    How you are storing the data in the database as String / Date?
    How you are retrieve the data from the database String / Date ?
    Following are some the tips you can check.
    1. If you are storing the data as date format then there is no issue you will get proper data.
    2. Check the date format in the storing part and also retrieving part.
    3. If you are storing the date as string. you have to "CAST" or "Covert" in the retrieving part.
    4. You can use the DATEPART in the retrieve section of the date and compare and concatenate.

    By Nathan
    Direction is important than speed

  • #767720
    Just define an single element array and use .push method to make it array with required elements, see below snippet

    var fruits = ["Banana", "Orange", "Apple", "Mango"];
    fruits.push("Kiwi");

    The push() method adds new items to the end of an array, and returns the new length.
    Note: The new item(s) will be added at the end of the array.
    Note: This method changes the length of the array.
    Tip: To add items at the beginning of an array, use the unshift() method.

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


Sign In to post your comments