locked
String was not recognized as a valid DateTime RRS feed

  • Question

  • User-1352156089 posted

    Hi All,

    I have 2 date-pickers built in JQuery which are supposed to filter into a datalist some data by dates. 

    I have set the JQuery to add the dates selected into the 2 textboxes in the following format: dd/mm/yy

    My SQL database has the ISO standard date format yyyy-MM-dd

    I am trying to query by database unsuccessfully with the following code behind:

    Public Sub Bind()
            If Not String.IsNullOrEmpty(Textbox1.Text.Trim()) And Not String.IsNullOrEmpty(Textbox2.Text.Trim()) Then
    
                Dim cmd As SqlCommand = New SqlCommand("select distinct DateColumnID, day, dayname, Year from dbo.DateTable where Date BETWEEN @StartDate AND @EndDate order by DateColumnID ASC", con)
                Dim SD = DateTime.ParseExact(Textbox1.Text.Trim(), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)
                Dim SDF As DateTime = Convert.ToDateTime(SD)
                Dim ED = DateTime.ParseExact(Textbox2.Text.Trim(), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)
                Dim EDF As DateTime = Convert.ToDateTime(ED)
                cmd.Parameters.AddWithValue("@StartDate", SqlDbType.Date).Value = SDF
                cmd.Parameters.AddWithValue("@EndDate", SqlDbType.Date).Value = EDF
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim ds As DataSet = New DataSet()
                da.Fill(ds, "dbo.DateTable")
                CalendarDateDatalist.DataSource = ds.Tables(0)
                CalendarDateDatalist.DataBind()
            End If
    End Sub

    Could you please help me to understand what's wrong with the above code?

    Thank you,
    Claudio

    Monday, February 11, 2019 6:42 PM

Answers

  • User753101303 posted

    Hi,

    It seems it's perhaps some kind of misunderstanding caused by having SSMS showing dates using the yyyy-MM-dd format.

    ParseExact is to convert an input string to a DateTime. Check Textbox1.Text and Textbox2.Text to see which value you try to convert but it seems you are telling us those strings are using the "dd/MM/yy" format (NOT "yyyy-MM-dd").

    Then by using Convert.ToDateTime you are converting a DateTime to a DateTime which is useless. You can delete those lines. Then  you are correctly using parameters, providing native datetime values which the way to go.

    Make sure to understand the difference between the "actual" datetime value (the same value is stored the same way for everyone) and how it is converted to (or from) a string (the problem being that each country can have its own way of showing dates). SSMS uses yyyy-MM-dd just because it's better to show developers or DBAs something that is not ambiguous (ie the same that is shown the same way for all developers or DBAs, it doesn't prevent to use a particular country convention when it is converted to a string for being shown in a web application).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 11, 2019 7:16 PM
  • User475983607 posted
    JQuery runs in the browser after the code behind runs. Set the default in the code behind. If you user the debuggers, Visual Studio and dev tools, you can see the execution order.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 11, 2019 10:52 PM

All replies

  • User475983607 posted

    Claudio7810

    I have set the JQuery to add the dates selected into the 2 textboxes in the following format: dd/mm/yy

    Could you please help me to understand what's wrong with the above code?

    The error message is very clear.

    dd/mm/yy is the input format which not the same format that you specified set in the ParseExact() method.  

    Dim SD = DateTime.ParseExact(Textbox1.Text.Trim(), "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)
                Dim SDF As DateTime = Convert.ToDateTime(SD)

    Use the same format throughout.

    Dim SD = DateTime.ParseExact(Textbox1.Text.Trim(), "dd/MM/yy", System.Globalization.CultureInfo.InvariantCulture)
                Dim SDF As DateTime = Convert.ToDateTime(SD)

    Monday, February 11, 2019 6:55 PM
  • User753101303 posted

    Hi,

    It seems it's perhaps some kind of misunderstanding caused by having SSMS showing dates using the yyyy-MM-dd format.

    ParseExact is to convert an input string to a DateTime. Check Textbox1.Text and Textbox2.Text to see which value you try to convert but it seems you are telling us those strings are using the "dd/MM/yy" format (NOT "yyyy-MM-dd").

    Then by using Convert.ToDateTime you are converting a DateTime to a DateTime which is useless. You can delete those lines. Then  you are correctly using parameters, providing native datetime values which the way to go.

    Make sure to understand the difference between the "actual" datetime value (the same value is stored the same way for everyone) and how it is converted to (or from) a string (the problem being that each country can have its own way of showing dates). SSMS uses yyyy-MM-dd just because it's better to show developers or DBAs something that is not ambiguous (ie the same that is shown the same way for all developers or DBAs, it doesn't prevent to use a particular country convention when it is converted to a string for being shown in a web application).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 11, 2019 7:16 PM
  • User-1352156089 posted

    Thank you Patrice, I pointed me towards the right directtion:

    Public Sub Bind()
            If Not String.IsNullOrEmpty(Textbox1.Text.Trim()) And Not String.IsNullOrEmpty(Textbox2.Text.Trim()) Then
    
                Dim cmd As SqlCommand = New SqlCommand("select distinct DateColumnID, day, dayname, Year from dbo.DateTable where Date BETWEEN @StartDate AND @EndDate order by DateColumnID ASC", con)
                cmd.Parameters.AddWithValue("@StartDate", Convert.ToDateTime(Textbox1.Text.Trim()))
                cmd.Parameters.AddWithValue("@EndDate", Convert.ToDateTime(Textbox2.Text.Trim()))
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim ds As DataSet = New DataSet()
                da.Fill(ds, "dbo.DateTable")
                CalendarDateDatalist.DataSource = ds.Tables(0)
                CalendarDateDatalist.DataBind()
            End If
        End Sub

    However, I cannot figure out (I am quite new) why the query works only if

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Bind()
        End Sub

    and not on Pageload:

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            If Not IsPostBack Then
                If Not String.IsNullOrEmpty(Textbox1.Text.Trim()) And Not String.IsNullOrEmpty(Textbox2.Text.Trim()) Then
                    Bind()
    
                End If
    
            End If
        End Sub

    I guess that this is due to the fact that the initial dates are retrieved from a Jquery code.

    How do you suggest to accomplish that?

    Thanks

    Monday, February 11, 2019 9:32 PM
  • User475983607 posted

    I guess that this is due to the fact that the initial dates are retrieved from a Jquery code.

    Try running your code through the debugger.  This IF...

     If Not IsPostBack Then

    ... run when the page first loads so the textboxes are empty.

    How do you suggest to accomplish that?

    What are you trying to accomplish?

    Monday, February 11, 2019 9:59 PM
  • User-1352156089 posted
    Thank you for your feedback.

    I m trying to load the datalist with the data in the time range that is set by default in the 2 textboxes by the Jquery at page load.

    Thank you

    Monday, February 11, 2019 10:19 PM
  • User475983607 posted
    JQuery runs in the browser after the code behind runs. Set the default in the code behind. If you user the debuggers, Visual Studio and dev tools, you can see the execution order.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 11, 2019 10:52 PM