Results 1 to 7 of 7

Thread: [RESOLVED] Date Format in SQL string

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2022
    Posts
    33

    Resolved [RESOLVED] Date Format in SQL string

    Could someone provide some insight into how to format dates in an SQL string in my code? If I copy the statement into MySQL query editor (and hard code the date instead of a variable), it works fine. This leads me to believe that my code must be incorrect. Here is what I have:

    Code:
    Dim thisDate As Date = Date.Today
    MessageBox.Show(thisDate)
    
    "SELECT SUM(quantity) 
           FROM order_details
           LEFT JOIN orders ON orders.order_no = order_details.order_no 
           WHERE order_details.process = 'CO2 Laser' AND order_details.status = 'Open' AND orders.ship_date >" & thisDate & ";"
    The date in the MySQL database is stored as DATE and is formatted yyyy-MM-dd in the table. The variable is output as MM-dd-yyy. I tried this

    Code:
    Dim thisDate As Date = Date.Today
    Format(thisDate, "yyyy-MM-dd"
    MessageBox.Show(thisDate)
    however the message box output was unchanged. Additionally, it was suggested that I parameterize the date. I saw an example, but it was not stored as a string. How would I go about this (if it is better). Any assistance would be greatly appreciated.

  2. #2
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Date Format in SQL string

    Quote Originally Posted by dccjr3927 View Post
    Could someone provide some insight into how to format dates in an SQL string in my code? If I copy the statement into MySQL query editor (and hard code the date instead of a variable), it works fine. This leads me to believe that my code must be incorrect. Here is what I have:

    Code:
    Dim thisDate As Date = Date.Today
    MessageBox.Show(thisDate)
    
    "SELECT SUM(quantity) 
           FROM order_details
           LEFT JOIN orders ON orders.order_no = order_details.order_no 
           WHERE order_details.process = 'CO2 Laser' AND order_details.status = 'Open' AND orders.ship_date >" & thisDate & ";"
    The date in the MySQL database is stored as DATE and is formatted yyyy-MM-dd in the table. The variable is output as MM-dd-yyy. I tried this

    Code:
    Dim thisDate As Date = Date.Today
    Format(thisDate, "yyyy-MM-dd"
    MessageBox.Show(thisDate)
    however the message box output was unchanged. Additionally, it was suggested that I parameterize the date. I saw an example, but it was not stored as a string. How would I go about this (if it is better). Any assistance would be greatly appreciated.
    A better option would be to use a parameterised query, not string concatenation. Parameters remove this problem entirely as well as being more secure.

    If you search the forums you will find plenty of threads on the topic!

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Date Format in SQL string

    1) as noted, you should be using parameter, not concatenating string. Then the format wouyldn't matter.
    2) Dates are just a value, they don't have an inherent format... the format you see in the db is just a display format. It doesn't affect the value.
    3) If, for what ever reason you insist on using a concatenated string, dates are represented by ' around them: where blahField = '2020-11-4' ... etc ... But if you use parameters, you wouldn't need to worry about that.
    4) If, for what ever reason you insist on using a concatenated string, you should make sure the format of the date in the query cannot be misinterpreted - the ISO format of YYYY-MM-DD works best IMHO - to avoid getting Feb 3 when you meant Mar 2


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Member
    Join Date
    Aug 2022
    Posts
    33

    Re: Date Format in SQL string

    Thanks. I am going to use parameterized queries. I am not sure how to do this in a string. My queries are in a dictionary like this
    Code:
    Dim WCtbx As New Dictionary(Of TextBox, String) From {
                {tbxOpenCO2, "SELECT SUM(quantity) FROM order_details LEFT JOIN orders ON orders.order_no = order_details.order_no WHERE order_details.process = 'CO2 Laser' AND order_details.status = 'Open' AND orders.ship_date > @thisDate;"},
                {tbxOpenFiber, "SELECT SUM(quantity) FROM order_details LEFT JOIN orders ON orders.order_no = order_details.order_no WHERE order_details.process = 'Fiber Laser' AND order_details.status = 'Open' AND orders.ship_date > @thisDate;"},
    }
    And this is the loop
    Code:
    For Each ky In WCtbx.Keys
                    cmd.Parameters.AddWithValue("@thisDate", thisDate)
                    cmd.CommandText = WCtbx(ky)
                    ky.Text = cmd.ExecuteScalar
                Next
    I do not know where or how to set the parameter. I tried stepping through my code and the parameter is not in the query statement. Could you clarify the use of the parameter in this instance. Thank you for your assistance.

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Date Format in SQL string

    why 2 queries?
    add the Filter criteria to a Combobox...
    CO2 Laser
    Fiber Laser

    then you just need one query and the user can choose from the Combobox

    the rest of the query looks the same to me, regarding the Date
    you allready have the answer(s)
    Last edited by ChrisE; Nov 5th, 2022 at 04:47 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Member
    Join Date
    Aug 2022
    Posts
    33

    Re: Date Format in SQL string

    There are more than 2 queries (there's actually 9 in this section). I just included these two for illustration purposes. The result from each query show the quantity of active orders for each work center. I just don't know how to get the parameter into the SQL string.

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Date Format in SQL string

    you have the parameter in the query ... that's what the @thisDate is ... that's the parameter ... and you set it just like you are.... using .AddWithValue ...


    BUT ... because you have it in a loop, you're adding it more than once. If you're going to use the same value each time... add it ONCE. Because you're not creating new command objects in the loop, it's just going to keep adding the parameter over and over and over. There's no need to do that, especially since the vaue doesn't change. Add the value, start the loop, set the commandText, execute it, and loop.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width