locked
Trouble with date formats & conversions as part of parameterised query RRS feed

  • Question

  • User1060443753 posted

    Hi all,

    I am sure this is a simple one, but I have given myself a headache trying to sort it out. Can someone point me in the right direction!?

    I have a VB.net intranet page with a couple of datapickers, the selected values of which are passed into a sql query to return data for a gridview on the page.

    It all works perfectly - except for the fact that the datepicker returns the format YYYY-MM-DD, but the SQL query is reading it as YYYY-DD-MM. So anything later than the 12th day of the month errors, and obviously the wrong date range is being returned regardless.

    This is the sub that's setting the parameters and firing off the stored procedure.

    ---------------------------------------

    Protected Sub Refresh2_click(sender As Object, e As EventArgs) Handles Me.Load

    Dim constr As String = ConfigurationManager.ConnectionStrings("constr3").ConnectionString
    Using conn As New SqlConnection(constr)
    conn.Open()
    Dim exec As SqlCommand = New SqlCommand("MAXP030inrt.dbo.uspUjbotif", conn)
    exec.Parameters.AddWithValue("@DateFrom", datepicker1.value)
    exec.Parameters.AddWithValue("@DateTo", datepicker2.value)
    exec.Parameters.AddWithValue("@Customer", "%" & ddlCustomer.text & "%")
    exec.Parameters.AddWithValue("@ProdGrp", "%" & ddlProdGrp.text & "%")
    exec.CommandType = CommandType.StoredProcedure
    exec.ExecuteReader()
    End Using

    End Sub

    -------------------------------------

    This is the pertinent part of the SQL stored procedure:

    --------------------------------------

    where
    a.[oemer_reqdate] >= @DateFrom AND a.[oemer_reqdate] <= @DateTo

    --------------------------------------

    I'm struggling to identify where to put a conversion, and what that conversion would look like.

    Any help!? TIA

    JB

    (Reason for edits: formatting issues!)

    Friday, February 9, 2018 12:37 PM

Answers

  • User1400794712 posted

    Hi jb2_86_uk,

    Since the SQL query is reading datetime as YYYY-DD-MM, you can pass the value of datepicker as the format YYYY-DD-MM.

    <head runat="server">
        <title></title>
        <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
        <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
        <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
        <script>
            $(function () {
                $("#datepicker1").datepicker({ dateFormat: 'yy-dd-mm' }); //Modify the datetime format
                $("#datepicker2").datepicker({ dateFormat: 'yy-dd-mm' }); //Modify the datetime format
            });
        </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <p>Date1:
                    <asp:TextBox ID="datepicker1" runat="server"></asp:TextBox></p>
                <p>Date2:
                    <asp:TextBox ID="datepicker2" runat="server"></asp:TextBox></p>
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click"/>
            </div>
        </form>
    </body>

    Then, the datetime will be pass as format YYYY-MM-DD.

    Best Regards,

    Daisy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 12, 2018 9:10 AM

All replies

  • User1400794712 posted

    Hi jb2_86_uk,

    Since the SQL query is reading datetime as YYYY-DD-MM, you can pass the value of datepicker as the format YYYY-DD-MM.

    <head runat="server">
        <title></title>
        <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
        <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
        <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
        <script>
            $(function () {
                $("#datepicker1").datepicker({ dateFormat: 'yy-dd-mm' }); //Modify the datetime format
                $("#datepicker2").datepicker({ dateFormat: 'yy-dd-mm' }); //Modify the datetime format
            });
        </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <p>Date1:
                    <asp:TextBox ID="datepicker1" runat="server"></asp:TextBox></p>
                <p>Date2:
                    <asp:TextBox ID="datepicker2" runat="server"></asp:TextBox></p>
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click"/>
            </div>
        </form>
    </body>

    Then, the datetime will be pass as format YYYY-MM-DD.

    Best Regards,

    Daisy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 12, 2018 9:10 AM
  • User1060443753 posted

    Thank you Daisy. That was embarrassingly simple! I'll blame it on a tough week and code snow-blindness!

    Cheers

    JB

    Monday, February 12, 2018 9:44 AM