Results 1 to 8 of 8

Thread: Convert from string to date

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    11

    Convert from string to date

    Hi in advance, thanks for the help I have the following drawback.

    My application allows you to import content from an excel file to a datagridview, with the purpose of then having all the contents saved to a DB, one of the columns has date type but with the format "MM / dd / yyyy", that date before sending it to the DB I enter it in a variable of type string, what I want is to change the format to "dd / MM / yyyy" and to be able to send it to the DB with that format, the code I use is the following:

    Dim date As String = ""
    date = row.Cells ("F1"). Value
    Dim Format As String = ""
    format = Format (CDate(date), "dd / MM / yyyy")
    add.Parameters.AddWithValue ("@date", Convert.ToDateTime (format))

    --The error sent me when evaluating the date "09/13/2017"

    I appreciate every help provided.

  2. #2
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: Convert from string to date

    Have a look on this:

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim DATE_ As Date = Now.ToShortDateString
            Dim C_US As New CultureInfo("en-US")
            Dim C_BE As New CultureInfo("fr-BE")
            Dim C_DE As New CultureInfo("de-DE")
    
            MsgBox(DATE_.ToString("d", C_US) & vbNewLine & DATE_.ToString("d", C_BE) & vbNewLine & DATE_.ToString("d", C_DE))
    
        End Sub

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

    Re: Convert from string to date

    I want is to change the format to "dd / MM / yyyy" and to be able to send it to the DB with that format
    Why?
    If the field in the database is a Date, then leave it alone... just send it what it is expecting... a Date. Nothing more. Nothing less.
    You're trying to take the data 9/13/2017 and re-format it to 13/9/2017 which is an invalid date as far as the database is concerned. There is no 13th month. Please don't confuse the value of a date with the format of a date.

    -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
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: Convert from string to date

    I belive his issue is that he gets the Date with a US format, but he needs to store it in a database that has a diferent regional setting.
    He can use something like this to get the date converted to his regional settings:

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim MyCulture As CultureInfo = Thread.CurrentThread.CurrentCulture
    
            Try
    
    
                Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US")
                Dim C_ES As New CultureInfo("es-ES")
                Dim DATE_ As DateTime = Convert.ToDateTime("09/02/2017")
    
                MessageBox.Show(String.Format("es-ES: {0}", DATE_.ToString("d", C_ES)))
    
                Thread.CurrentThread.CurrentCulture = MyCulture
    
            Catch ex As Exception
                MessageBox.Show(String.Format("Ex Message: {0} {1} StackTrace: {2}", ex.Message, vbNewLine, ex.StackTrace))
                Thread.CurrentThread.CurrentCulture = MyCulture
            End Try
    
        End Sub

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

    Re: Convert from string to date

    saved to a DB, one of the columns has date type but with the format "MM / dd / yyyy"
    I read this to mean that the database has a date field and is formatted "mm/dd/yyyy" which is a square hole... and he's trying to make a square peg fit in.

    Maybe it would help to know what DBMS is being used...

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

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Convert from string to date

    And what the field type is. So often, people store dates as strings (of whatever sort the DB has). That ultimately puts some severe limits on the DB while not helping anybody. Still, if one takes over a legacy database, one may not have the luxury of fixing problems.
    My usual boring signature: Nothing

  7. #7
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: Convert from string to date

    lets see what he explains about his problem, the way i initialy interpretate it was that the column in the excel file had format "MM / dd / yyyy" and on the DB "dd / MM / yyyy". but as i mation before i m not native english speaker, may be wrong.

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

    Re: Convert from string to date

    This code is silly:
    Code:
    format = Format (CDate(date), "dd / MM / yyyy")
    add.Parameters.AddWithValue ("@date", Convert.ToDateTime (format))
    You start with a String, convert it to a Date, convert that to a String and then convert that to a Date again. Obviously you can get rid of the last two conversions If the highlighted part works then that's all you need, because it converts the original String to a Date.
    Code:
    add.Parameters.AddWithValue ("@date", CDate(date))
    If that part doesn't work then you need to specify the format of the initial String in order to make it work. In that case, you would call Date.ParseExact if you know for sure that the String is valid or Date.TryParseExact if it might not be. Take note that ParseExact returns a Date so you can plug it straight into that code but TryParseExact does not, so be sure to read up on how to use it if you go that way.

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