Member Avatar for cirno

Hello again, codegurus.

I need to ask your assistance in converting string to datetype, from 'dd/mm/yyyy' to 'yyyy-mm-dd'.

The user could key in either the 'dd/mm/yyyy' format or 'yyyy-mm-dd' format in a textbox.
the system would check for both format and convert both to the 'yyyy-mm-dd' date format, to be able to save the value in a datetype field in a MySQL table.

I am confused, and testing some codes from google search still doesn't give me a solution.

Thanks in advance.

  • 3 Contributors
  • 3 Replies
  • 5K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by cirno

Dani AI

Generated

If users can type either dd/MM/yyyy or yyyy-MM-dd, parse the text into a DateTime using an explicit list of formats and InvariantCulture, then output a single canonical form. Avoid implicit string-to-date conversions (turn Option Strict On) and do not rely on the machine’s CurrentCulture, which can misinterpret day and month.

Imports System.Globalization

Function NormalizeDate(input As String) As String
    Dim formats = New String() {"dd/MM/yyyy", "yyyy-MM-dd"}
    Dim dt As DateTime
    If DateTime.TryParseExact(input.Trim(),
                              formats,
                              CultureInfo.InvariantCulture,
                              DateTimeStyles.None,
                              dt) Then
        Return dt.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)
    Else
        Throw New FormatException("Enter date as dd/MM/yyyy or yyyy-MM-dd.")
    End If
End Function

For MySQL, prefer parameters over inserting formatted strings. Let ADO.NET send a real date value and MySQL handle the storage. This avoids locale issues and invalid dates like 31/02/2010.

' dt is the DateTime returned by TryParseExact
Using conn As New MySql.Data.MySqlClient.MySqlConnection(connString),
      cmd As New MySql.Data.MySqlClient.MySqlCommand(
          "INSERT INTO your_table (your_date_col) VALUES (@d);", conn)

    cmd.Parameters.Add("@d", MySql.Data.MySqlClient.MySqlDbType.Date).Value = dt.Date
    conn.Open()
    cmd.ExecuteNonQuery()
End Using

Notes:

  • Trim input and validate; show a clear message when parsing fails.
  • If you must display the date back to users, format with ToString("dd/MM/yyyy", CultureInfo.InvariantCulture) for consistency.

Recommended Answers

All 3 Replies

Member Avatar for bhagawatshinde

if u want to retrieve date as string to value then
use this

Dim myDate As DateTime = DateTime.Now
   datetimepicker.Value = DateTime.ParseExact(dr.Item("product_date"), "dd/MM/yyyy", System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.None)
Edited by kvprajapati because: Added [code] tags.
Member Avatar for codeorder 
Dim dt As Date = "12/13/2010"
        MsgBox(Format(dt, "yyyy-MM-dd"))
Member Avatar for cirno

I used

txt2.Text = str2date1(txt1.Text)

where

Function str2date1(ByVal strInputText As String) As String

        Dim dt As Date = strInputText
        Return (Format(dt, "yyyy-MM-dd"))

    End Function

Thank you so much for the helpful tips, bhagawatshinde and codeorder.

Edited by cirno because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.