How do I convert a uk date in string format to date?

• Lorna-331036

  SSC Eights!

  Points: 867

  More actions

  February 1, 2019 at 11:46 am

  #2020894

  Thanks guys - I was always testing that the date was valid first, but I've lost the will to live on this one (lol), so created another sproc and my .net c# code is (which works a treat :-)):

    protected void txtSearch_TextChanged(object sender, EventArgs e)
    {
     // Get a reference to the master page
     MasterPage ctl00 = FindControl("ctl00") as MasterPage;
     // Get a reference to the ContentPlaceHolder
     ContentPlaceHolder MainContent = ctl00.FindControl("MainContent") as ContentPlaceHolder;
     SqlDataSource SqlDSSales = (SqlDataSource)MainContent.FindControl("SqlDSSales");
     string inputString = Server.HtmlEncode(txtSearch.Text);

     if (inputString != "")
     {
      pnlSearchResults.Visible = true;
      if (SqlDSSales != null)
      {
        //determine if a date has been entered
        DateTime orderDate;

        if (DateTime.TryParse(inputString, out orderDate))
        {
         String.Format("{0:dd/MM/yyyy}", orderDate);
         SqlDSSales.SelectCommand = "spSalesOrdersSearchDate";
         //remove the parameter first in case it's a string type
         SqlDSSales.SelectParameters.Remove(SqlDSSales.SelectParameters["searchTerm"]);
         SqlDSSales.SelectParameters.Add("searchTerm", DbType.DateTime, inputString);
        }
        else
        {
         //not valid date format, so will have to assume it's a string
         SqlDSSales.SelectCommand = "spSalesOrdersSearch";
         SqlDSSales.SelectParameters.Remove(SqlDSSales.SelectParameters["searchTerm"]);
         SqlDSSales.SelectParameters.Add("searchTerm", DbType.String, inputString);
         //SqlDSSales.SelectParameters["searchTerm"].DbType = DbType.String;
        }

        SqlDSSales.SelectParameters["searchTerm"].DefaultValue = inputString;
       
        gvSalesList.DataBind();
      }
      
      else
        txtSearch.Text = "Please enter text";
     }
    }

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  February 3, 2019 at 6:49 pm

  #2020990

  Lorna-331036 - Thursday, January 31, 2019 4:46 AM

  Hi
  A user enters a date in uk format (i.e '24/01/2019') into a web form (.net).  I then call my stored procedure to search records with a date field containing this date.  Please can someone tell me why this is wrong as I'm getting the SqlException: Conversion failed when converting date and/or time from character string.  Here is my stored procedure:

  [spSalesOrdersSearchDate]
  (
  @searchTerm varchar(10)
  )
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    SELECT *
      FROM vSales
      WHERE (Order_Status = 0 OR Order_Status = 4 OR Order_Status = 5)
      AND Delivery_Date = CONVERT(Date, @searchTerm,103)
      ORDER BY Company_Name,Order_Ref_No
  END

  What is the datatype of the "Delivery_Date" column in the vSales view for this code?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)