locked
how to convert date time to varchar RRS feed

  • Question

  • User-564804143 posted

    hello friends i m getting error that:

    System.Data.SqlClient.SqlException: Error converting data type date to varchar.

    because in database coloumn name in datetime and i am taking date in table as varchar.

    below is my query 

     if (branch == "All" && type == "All")
      strsql = "select Airwaybill,address1,address2,address3,pincode,[Shipper Name],Pieces from coddatadetails where created_date>='" + txt_fromdate.Text + "' and created_date<='" + txt_todate.Text + "'";


    Saturday, May 11, 2013 3:53 AM

Answers

  • User-564804143 posted

    i m getting :2013/05/10

    i tried below but not working

    DateTime fromdate, todate;
            string fdate = "", tdate = "";
            fdate = txt_fromdate.Text.ToString();
            tdate = txt_todate.Text.ToString();
           
            fromdate = DateTime.ParseExact(fdate, "yyyy-MM-dd hh:mm:ss",System.Globalization.CultureInfo.CurrentCulture);
            todate = DateTime.ParseExact(tdate, "yyyy-MM-dd hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture);
            if (branch == "All" && type == "All")
               
     strsql = "select Airwaybilladdress1,address2,address3,pincodePieces from codsoftdatadetails where created_date>='" + fromdate + "' and created_date<='" + todate + "'";

    gettinig error:

     System.FormatException: String was not recognized as a valid DateTime.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 11, 2013 4:36 AM
  • User-1780421697 posted

    There is "Format" property of calender control you can use it.Whatever format you want.

    When getting date parameter  in database use the CONVERT function along with parameter

    http://www.w3schools.com/sql/func_convert.asp

    Data Format Parameter

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 11, 2013 5:08 AM

All replies

  • User1320044578 posted

    You can insert varchar value to date time in sql server. what format of date are you inputed in textbox

    Saturday, May 11, 2013 4:01 AM
  • User-1780421697 posted

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

     

    CAST(ColumnName AS varchchar(100))

    Saturday, May 11, 2013 4:06 AM
  • User-564804143 posted

    i m getting :2013/05/10

    i tried below but not working

    DateTime fromdate, todate;
            string fdate = "", tdate = "";
            fdate = txt_fromdate.Text.ToString();
            tdate = txt_todate.Text.ToString();
           
            fromdate = DateTime.ParseExact(fdate, "yyyy-MM-dd hh:mm:ss",System.Globalization.CultureInfo.CurrentCulture);
            todate = DateTime.ParseExact(tdate, "yyyy-MM-dd hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture);
            if (branch == "All" && type == "All")
               
     strsql = "select Airwaybilladdress1,address2,address3,pincodePieces from codsoftdatadetails where created_date>='" + fromdate + "' and created_date<='" + todate + "'";

    gettinig error:

     System.FormatException: String was not recognized as a valid DateTime.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 11, 2013 4:36 AM
  • User-1780421697 posted

    if you are only interested in dates.

    http://www.ajaxcontroltoolkit.com/Calendar/Calendar.aspx

    Associate Calender Extender with text box and set the format what ever you want.

    Step 2: Get Date from textboxes and In Sql commnad

    use BETWEEN Operator like

    strsql = "select Airwaybilladdress1,address2,address3,pincodePieces from codsoftdatadetails where

    created_date  BETWEEN  '" + fromdate +" AND " + todate + "'";

    Saturday, May 11, 2013 4:51 AM
  • User-564804143 posted

    Hi,

    i already use calender control exrtender and getting date as YYYY-mm-dd formate and in database date store as YYYY-mm-dd HH:MM:SS formate.

    i am tring to convert as my previous code bt unable to do.

    please help me.

    Saturday, May 11, 2013 5:00 AM
  • User-1780421697 posted

    There is "Format" property of calender control you can use it.Whatever format you want.

    When getting date parameter  in database use the CONVERT function along with parameter

    http://www.w3schools.com/sql/func_convert.asp

    Data Format Parameter

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 11, 2013 5:08 AM
  • User-564804143 posted

    yes i already used below formate but still getting error:

    fdate = "convert(varchar,'" + txt_fromdate.Text.ToString() + "',120)";
    tdate = "convert(varchar,'" + txt_todate.Text.ToString() + "',120)";

    DateTime fromdate = DateTime.ParseExact(fdate, "yyyy-MM-dd hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture);

    now using this fromdate variable in query. but getting above error.

    and while using 

       DateTime fromdate=Convert.ToDateTime(txt_fromdate.Text.ToString());

    strsql = "select Airwaybill,,Pieces from codsoftdatadetails where created_date between '" + fromdate + "' and '" + todate + "'";

    getting error:System.Data.SqlClient.SqlException: Invalid object name 'codsoftdatadetails'

    where i am wrong unable to findout.

    Saturday, May 11, 2013 5:18 AM
  • User-1716253493 posted
    use parameters: strsql = "select Airwaybilladdress1,address2, address3,pincodePieces from codsoftdatadetails where created_ date>=@fromdate and created_date<=@todate" ;
    Saturday, May 11, 2013 5:28 AM
  • User-564804143 posted

    not working

    Saturday, May 11, 2013 5:49 AM
  • User-800912061 posted

    Please try below code.

    DateTime fromdate = DateTime.ParseExact(txt_fromdate.Text, "yyyy/MM/dd HH:mm tt", null);
    
    DateTime todate = DateTime.ParseExact(txt_todate.Text, "yyyy/MM/dd HH:mm tt", null);
    
    if (branch == "All" && type == "All")
    {
        string strSql = "SELECT Airwaybilladdress1, address2, address3, pincodePieces from codsoftdatadetails WHERE created_date>='" + fromdate + "' and created_date<='" + todate + "'";
    } 



    Saturday, May 11, 2013 5:52 AM
  • User-564804143 posted

    sorry guys i have datetime coloumn "cretated_date" in database as "May 10 2013  1:30PM"  formate and getting date from ajax calender extender as YYYY-MM-DD formate.

    i tried to convert textbox value to date time but unable to get solution. kindly help me to solve this issue.

    Saturday, May 11, 2013 5:56 AM
  • User-800912061 posted

    Please try below code.

    if (branch == "All" && type == "All")
    {
    string strSql = "SELECT Airwaybilladdress1, address2, address3, pincodePieces from codsoftdatadetails WHERE CONVERT(VARCHAR(26), created_date, 109) >='" + CONVERT(VARCHAR(26), txt_fromdate.Text, 109) + "' and CONVERT(VARCHAR(26), created_date, 109) <='" + CONVERT(VARCHAR(26), txt_todate.Text, 109) + "'";
    }


    Saturday, May 11, 2013 6:12 AM
  • User-564804143 posted

    Hello nikhil got previous error: System.FormatException: String was not recognized as a valid DateTime.

    in this line 

    DateTime fromdate = DateTime.ParseExact(txt_fromdate.Text, "yyyy/MM/dd HH:mm tt", null);
    Saturday, May 11, 2013 6:16 AM
  • User-800912061 posted

    In a custom date format string, / denotes the culture-specific date separator, not the literal character /. Thus, the result of your code depends on the user's (or the server's) localization settings.

    To make your code independent of culture-specific settings, you have two options:

    • Explicitly specify a culture that uses a slash as the date separator, e.g.

    DateTime.ParseExact(txtTourStartDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture)

    • or escape the character, e.g.

    DateTime.ParseExact(txtTourStartDate.Text, @"dd\/MM\/yyyy", null)
    (note the @ and the \). Both should yield the desired result.

    Or try to modify your code like this.

    DateTime fromdate = DateTime.ParseExact(txt_fromdate.Text.Tostring(), "yyyy/MM/dd HH:mm tt",  CultureInfo.InvariantCulture);

    Or

    DateTime fromdate = DateTime.ParseExact(txt_fromdate.Text.Tostring(), "yyyy\/MM\/dd HH:mm tt", null);

    Saturday, May 11, 2013 6:28 AM
  • User-564804143 posted

    hello nikhil,

    i already tried above methods as u told but got same error. u can check my previous posts.

    Saturday, May 11, 2013 6:37 AM
  • User-800912061 posted

    Atul,

    Could you please repost your code, where exactly you are getting the error message and what you trying to achieve from it. I have suggested all the solution on the basis of your code but all went wrong.

    Saturday, May 11, 2013 6:45 AM