Results 1 to 13 of 13

Thread: [RESOLVED] Parse date to correct format

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Resolved [RESOLVED] Parse date to correct format

    Hi

    I have the following code to save file info in a data table to a mysql database table via an adapter:
    Code:
    With cmd2.Parameters
        .Add("@Name", MySqlDbType.VarChar, 50, "Name")
        .Add("@LastWriteTime", MySqlDbType.DateTime, "LastWriteTime")
    End With
    My issue is with LastWriteTime. The format in the datatable is dd/MM/yyyy HH :mm:ss (eg. 02/07/2021 14:51:57). I know Mysql accepts date in yyyy-MM-dd format. I obviously have to Parse or TryParse in the above. After a lot of searching, I just cannot figure out how to apply it.

    Any help would be much appreciated.
    Last edited by dday9; Jul 6th, 2021 at 10:10 AM. Reason: formatted code

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Parse date to correct format

    Nooooooooooooooo! There is - at least there should be - no format. Format is ONLY an issue when you have a string containing a representation of a date/time. You should not have any such string, so format should not be an issue at all. Your DataTable should contain DateTime values, not Strings. Your database column should be data type datetime, which your parameter indicates it is. In that case, there is not format at either end so there is no issue with format. If there is an issue with format then that means that you are using strings to store date/time values and THAT is the problem you need to fix. Don't do the wrong thing in the first place and there is no format problem to solve.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Parse date to correct format

    Well I created a data table :

    Code:
    Dim dt As DataTable = New DataTable
    
           With dt
                .Columns.Add("Name", System.Type.GetType("System.String"))
                .Columns.Add("LastWriteTime", System.Type.GetType("System.DateTime"))
                .Columns.Add("LastCreationTime", System.Type.GetType("System.DateTime"))
    
    
            End With
    So I did set the datatable column to datetime.

    I then read data into the table with the following :

    Code:
    For Each file As IO.FileInfo In New IO.DirectoryInfo("D:\Wingerdbou\LutzvilleProgram\InvoerDataFiles\EZY Wine\Zipped").GetFiles
    
                Dim dr As DataRow = dt.NewRow
    
                dr(0) = file.Name
                dr(1) = file.LastWriteTime
                dr(2) = file.CreationTime
    
                dt.Rows.Add(dr)
            Next

    My assumtion that the fields LastWriteTime and LastCreationTime is of type datetime is then clearly wrong.
    Last edited by GideonE; Jul 6th, 2021 at 11:24 AM.

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

    Re: Parse date to correct format

    My assumtion that the fields LastWriteTime and LastCreationTime is of type datetime is then clearly wrong.
    First it shouldn't be an assumption. It should be checked against the documentation ... and yes, they are a dateTime...
    Which then leads to second, if your assumption that they are datetime is wrong, what is happening or not happening? Because they are datetime according to the documentation (which was easy enough to get to by typing ".net io.fileinfo" in to the search and pulling up the Microsoft Docs result at the top) ... then it should be fine. If it's not, then something else is wrong. But unless you tell us what that is, we can only guess... and you won't like the guesses we come up with or how to solve those problems.


    -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??? *

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Parse date to correct format

    Thank you for replying techgnome


    I get the following error : System.InvalidCastException: 'Conversion from string "LastWriteTime" to type 'Integer' is not valid.'

    at the following line;
    Code:
    .Add("@LastWriteTime", MySqlDbType.DateTime, "LastWriteTime")

    If I insert a break in my code and inspect the data table with the magnifying glass the data table seems fine with the data tablevisualizer.
    I must be missing something when adding the the command parameter?



    Regards
    Last edited by GideonE; Jul 6th, 2021 at 01:02 PM.

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

    Re: Parse date to correct format

    I wondered about that... The add isn't doing what you think it is doing... the parmeters for the .Add is 1) parameter name, 2) Parameter type 3) Parameter Size ... at least for the overlaod version you're using... so it's trying to use the literal value "LastWriteTime" as your parametter size... and as you're finding out, "LastWriteTime" isn't a number... but a string. It is NOT, as you're probably expecting, using the LastWriteTime column of your datatable. Why would it? The datatable is the datatable... and the command is the command... They are two separate objects that don't know a thing about each other. It's like opening a cookbook expecting the first recipe to be about tomato soup because that's what's on your TV.

    What you probably want to do is create the command, add the parameters - NO VALUES... and then loop through the rows of the datatable, set the values of the parameters to the values of the columns in the datatable, then execute the command. Wash, rinse, repeat.

    -tg

    edit thanks to wes for the correction on the thrid param
    Last edited by techgnome; Jul 6th, 2021 at 01:33 PM.
    * 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??? *

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Parse date to correct format

    Quote Originally Posted by GideonE View Post
    Thank you for replying techgnome


    I get the following error : System.InvalidCastException: 'Conversion from string "LastWriteTime" to type 'Integer' is not valid.'

    at the following line;
    Code:
    .Add("@LastWriteTime", MySqlDbType.DateTime, "LastWriteTime")

    If I insert a break in my code and inspect the data table with the magnifying glass the data table seems fine with the data tablevisualizer.
    I must be missing something when adding the the command parameter?



    Regards
    The third value in the "Add" method is "Size" and it expects an integer. You don't need that, try

    Code:
    .Add("@LastWriteTime", MySqlDbType.DateTime)
    https://docs.microsoft.com/en-us/dot...t-plat-ext-5.0

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Parse date to correct format

    Now that we have more information, the issue here is obvious:
    vb.net Code:
    1. With cmd2.Parameters
    2.     .Add("@Name", MySqlDbType.VarChar, 50, "Name")
    3.     .Add("@LastWriteTime", MySqlDbType.DateTime, "LastWriteTime")
    4. End With
    You can't just arbitrarily choose which parameters to use when calling a method. You MUST use the parameters that that method declares. If you are going to specify a source column for data when adding a parameter then you MUST specify a size. You're doing it correctly the first time but you have arbitratily ommitted the size the second time. If the data type doesn't need a size then just use zero:
    vb.net Code:
    1. With cmd2.Parameters
    2.     .Add("@Name", MySqlDbType.VarChar, 50, "Name")
    3.     .Add("@LastWriteTime", MySqlDbType.DateTime, 0, "LastWriteTime")
    4. End With

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Parse date to correct format

    Thank you all.

    Lessons learnt :

    1. When asking questions on the forum be specific about the error and include an error message
    2. When storing a date/datetime value in MySQL formatting is unnecessary. MySQL will store it as a date/datetime - albeit in a different format
    3. Read the Microsoft Doc with intent
    4. I have much to learn

    Ps. techgnome,, love the cookbook analogy. wes4dbt, thank you for the link (I will study it closely). jmchilhinney, your comment (If the data type doesn't need a size then just use zero) and date formatting lesson has had big impact.

    Regards

  10. #10
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Parse date to correct format

    Quote Originally Posted by GideonE View Post
    When storing a date/datetime value in MySQL formatting is unnecessary. MySQL will store it as a date/datetime - albeit in a different format
    Storing dates/times anywhere doesn't require formatting, if stored as binary date/time values. Most common databases have at least one binary date/time data type and many have several. SQLite is a notable exception but, even then, I believe that the ADO.NET provider can do the conversion for you. Binary date/time values are generally just numbers, often representing an offset from a specific point in time. If there is any translation to be done between your app and the database, the ADO.NET provider will handle it. You only need to consider format when storing dates/times as text, which you should NEVER do unless necessary.
    Quote Originally Posted by GideonE View Post
    jmchilhinney, your comment (If the data type doesn't need a size then just use zero) and date formatting lesson has had big impact.
    You can use the actual size in bytes of the data type if you like but I prefer to just use zero rather than try to remember those actual sizes. In fact, I think that you can use any value you like there as, if it's not a variable-sized data type, e.g. varchar and varbinary, I think that the size you specify is simply ignored. Some value is required by the .NET method though, so zero seems like an appropriate dummy. Maybe it would actually be most appropriate to use Nothing, which equates to zero for numeric data types but is visibly distinct when reading the code.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Parse date to correct format

    jmchilhinney thank you for clearing that up.

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

    Re: Parse date to correct format

    Quote Originally Posted by GideonE View Post
    Thank you all.

    Lessons learnt :

    1. When asking questions on the forum be specific about the error and include an error message
    2. When storing a date/datetime value in MySQL formatting is unnecessary. MySQL will store it as a date/datetime - albeit in a different format
    3. Read the Microsoft Doc with intent
    4. I have much to learn

    Ps. techgnome,, love the cookbook analogy. wes4dbt, thank you for the link (I will study it closely). jmchilhinney, your comment (If the data type doesn't need a size then just use zero) and date formatting lesson has had big impact.

    Regards
    In my signature is a link about removing eels from your hovercraft ... It's a thread on how to get effective help around here...


    Quote Originally Posted by jmcilhinney View Post
    Storing dates/times anywhere doesn't require formatting, if stored as binary date/time values. Most common databases have at least one binary date/time data type and many have several. SQLite is a notable exception but, even then, I believe that the ADO.NET provider can do the conversion for you. Binary date/time values are generally just numbers, often representing an offset from a specific point in time. If there is any translation to be done between your app and the database, the ADO.NET provider will handle it. You only need to consider format when storing dates/times as text, which you should NEVER do unless necessary.

    You can use the actual size in bytes of the data type if you like but I prefer to just use zero rather than try to remember those actual sizes. In fact, I think that you can use any value you like there as, if it's not a variable-sized data type, e.g. varchar and varbinary, I think that the size you specify is simply ignored. Some value is required by the .NET method though, so zero seems like an appropriate dummy. Maybe it would actually be most appropriate to use Nothing, which equates to zero for numeric data types but is visibly distinct when reading the code.
    And decimal... decimal is the other type that needs a specific size - as well as scale and precision (set separately) - if I remember right.

    -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??? *

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Parse date to correct format

    Thank you techgnome.

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