Remove From My Forums

Trouble with date formats & conversions as part of parameterised query

Question
0

Sign in to vote

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