locked
Adding Date to date Duration RRS feed

  • Question

  • User365103270 posted

    Good Evening Everyone!

    Can i ask how to add a duration of days... I want to do a system which has the function from "this date" to "that date"...

    anyone can assist?

    thanks!

    Tuesday, May 27, 2014 9:30 AM

Answers

  • User1140095199 posted

    Hi,

    jingjie28

    hi, thanks for the reply but what i actually want to do is to use the date picker and select the date and then update into the excel... anyone can assist?

    When you mention Datetimepicker I am assuming it's the jQuery Datetimepicker.

    Refer to the following example:

    Source:

        <form id="form1" runat="server">
        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
            <asp:Button ID="SaveBtn" runat="server" Text="SaveToExcel" OnClick="SaveBtn_Click" />
        </div>
        </form>

    In the HTML I have a TextBox1 that will get the datetime and SaveBtn that will save the data into an excelfile.

    jQuery: Datepicker

    http://jqueryui.com/datepicker/

    Now, the problem is jQuery datepicker needs to be modified to get datetime both.

    Refer to the following:

    http://jsfiddle.net/NbFa6/1/

    You may use the above code or the following:

    <head runat="server">
        <title></title>
        <link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css"/>
        <script src="//code.jquery.com/jquery-1.10.2.js"></script>
        <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
        <link rel="stylesheet" href="/resources/demos/style.css"/>
        <script>
            $(document).ready(function () {
                $("#TextBox1").datepicker();
                $("#TextBox1").datepicker("option", "dateFormat", "mm/dd/yy" + " " + getCurrentTime());
    
                function getCurrentTime() {
                    var date = new Date();
                            var hh = date.getHours();
                            var mm = date.getMinutes();
                            var ss = date.getSeconds();
                            var cdate = hh + ':' + mm + ':' + ss;
                            return cdate;
                }
            });        
        </script>
    </head>

    Finally save the data to excel.

    Use OledbConnection. You have to create a excel sheet with table with Dates column in it. You may use simple insert,update and delete statements just as SQL to work with excel too.

        protected void SaveBtn_Click(object sender, EventArgs e)
        {
            string sexcelconnectionstring = "";
            string path = Server.MapPath("~/DatesSheet.xlsx");
    
            //Use this if .xls
            // sexcelconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            //Use this if .xlsx
            sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes\"";
    
            using (OleDbConnection conn = new OleDbConnection(sexcelconnectionstring))
            {
                conn.Open();
                string query = "insert into [Sheet1$] values(@dt)";
                OleDbCommand ocmd = new OleDbCommand(query, conn);
                ocmd.Parameters.Add("@dt", OleDbType.DBDate).Value = TextBox1.Text;
                ocmd.ExecuteNonQuery();
            }
        }

    It's a working solution. Refer and modify as per your need.

    Saved to Excel:

    Hope it helps!

    Best Regards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 28, 2014 10:58 PM

All replies

  • User753101303 posted

    Hi,

    You are looking for http://msdn.microsoft.com/en-us/library/system.datetime.adddays(v=vs.110).aspx ? Not sure as this is quite asily discoverable. My understanding is that you want to add a duration in days to a date. Else try to give some details ("duration of days" ? or you want to substract two dates and have the corresponding duration in days ?)

     

     

    Tuesday, May 27, 2014 9:51 AM
  • User365103270 posted

    hi, thanks for the reply but what i actually want to do is to use the date picker and select the date and then update into the excel... anyone can assist?

    Tuesday, May 27, 2014 9:57 AM
  • User1140095199 posted

    Hi,

    jingjie28

    hi, thanks for the reply but what i actually want to do is to use the date picker and select the date and then update into the excel... anyone can assist?

    When you mention Datetimepicker I am assuming it's the jQuery Datetimepicker.

    Refer to the following example:

    Source:

        <form id="form1" runat="server">
        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
            <asp:Button ID="SaveBtn" runat="server" Text="SaveToExcel" OnClick="SaveBtn_Click" />
        </div>
        </form>

    In the HTML I have a TextBox1 that will get the datetime and SaveBtn that will save the data into an excelfile.

    jQuery: Datepicker

    http://jqueryui.com/datepicker/

    Now, the problem is jQuery datepicker needs to be modified to get datetime both.

    Refer to the following:

    http://jsfiddle.net/NbFa6/1/

    You may use the above code or the following:

    <head runat="server">
        <title></title>
        <link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css"/>
        <script src="//code.jquery.com/jquery-1.10.2.js"></script>
        <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
        <link rel="stylesheet" href="/resources/demos/style.css"/>
        <script>
            $(document).ready(function () {
                $("#TextBox1").datepicker();
                $("#TextBox1").datepicker("option", "dateFormat", "mm/dd/yy" + " " + getCurrentTime());
    
                function getCurrentTime() {
                    var date = new Date();
                            var hh = date.getHours();
                            var mm = date.getMinutes();
                            var ss = date.getSeconds();
                            var cdate = hh + ':' + mm + ':' + ss;
                            return cdate;
                }
            });        
        </script>
    </head>

    Finally save the data to excel.

    Use OledbConnection. You have to create a excel sheet with table with Dates column in it. You may use simple insert,update and delete statements just as SQL to work with excel too.

        protected void SaveBtn_Click(object sender, EventArgs e)
        {
            string sexcelconnectionstring = "";
            string path = Server.MapPath("~/DatesSheet.xlsx");
    
            //Use this if .xls
            // sexcelconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            //Use this if .xlsx
            sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes\"";
    
            using (OleDbConnection conn = new OleDbConnection(sexcelconnectionstring))
            {
                conn.Open();
                string query = "insert into [Sheet1$] values(@dt)";
                OleDbCommand ocmd = new OleDbCommand(query, conn);
                ocmd.Parameters.Add("@dt", OleDbType.DBDate).Value = TextBox1.Text;
                ocmd.ExecuteNonQuery();
            }
        }

    It's a working solution. Refer and modify as per your need.

    Saved to Excel:

    Hope it helps!

    Best Regards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 28, 2014 10:58 PM
  • User753101303 posted

    Hopefully Sam answer's will help. Else please give some more details (which Date picker, jQuery, Ajax control etc...) Is the problem to get the input from the date picker or is the problem in updating the Excel file or both ? If not both talk about your actual problem so that we spend time only on the exact point on which you need help.

    Finally please take care to tell what you actually want in your very first post. Form your first message I could never have guessed you wanted actually to get a date from a picker control and update an Excel file ??!!

    Thursday, May 29, 2014 8:22 AM
  • User365103270 posted

    Hi Everyone!

    The problem is solved!

    Thank you so much!

    Thursday, May 29, 2014 10:33 PM