Change MMDDYYYY to European format YYYYMMDD

[conerlysduck]

Hello,

I am trying to change MMDDYYYY to European format YYYYMMDD. I have this in my query:

"20" & Right([TABLE]![DATE],2) & Mid([TABLE]![DATE],3,2) & Left([TABLE]![DATE],2)

which gives me YYYYDDMM. Does anyone know to get the DD and MM switched so it reads YYYYMMDD?

[Frinavale]

When you retrieve a Date from a Database it will be a .NET DateTime Type. You can then use it's ToString method to format the date however you want. In your case you want it formatted as "YYYYMMDD" so you would provide that to the ToString method.


The default pattern that the DateTime.ToString() will returns is based on the user's cultural settings. This is set in the operating system. So if the user has a setting that follows the US date format it will return "MMDDYYYY" but if their cultural setting is French it will return "DDMMYYYY".....The underlying data for the DateTime is actually stored as a number.

This cultural setting is not only applied to dates. There is a way to over write the cultural setting of the operating system so that your application works in the cultural setting that you are expecting. There's a few ways to do this actually...

But I think that the ToString solution that I mentioned will work for your needs.

-Frinny

[kadghar]

Try assigning your table value to a date variable. Then write it down as you wish:

date1.year & date1.month & date1.day

you can create a function to assure the month and the day will be 2 digits even if they are <10, e.g. iff(a<10, "0" & a, a)

[conerlysduck]

Thank you for your response, but I do not understand the ToString solution that you are referring to. I need to know how to put it in an update query if you can. Thank you.

[Frinavale]

Is the column that you're using to store the date a "Date" type or is it a "VarChar" type?

If it's a "VarChar" type then you can call the ToString on the DateTime type to store the desired string into the database.

For example, if your column is a varchar then this will work:

Dim d As DateTime = DateTime.Now
 
Dim connectionString As String ="data source=<nameOfDatabaseServer>;initial catalog=<nameOfDatabase>;user id=<databaseUserId>;password=<passwordForDatabaseUser>"
Dim dbCon As SqlConnection
dbCon = New SqlConnection(connectionString) 
Dim sqlCom As New SqlCommand 
 
sqlCom.Connection = dbCon
sqlcom.CommandType = CommandType.Text
sqlCom.CommandText = "UPDATE my_table SET column1=@value"
sqlCom.Parmaeters.Add("@value", SqlDbType.VarChar).Value = d.ToString("YYYYMMDD")
 
Try
  dbCon.Open()
  sqlcom.ExecuteNonQuery
  dbCon.Close()
Catch ex As Exception
  MessageBox.Show("A problem occurred: " + ex.Message)
End Try 
 
 

Now, if the column storing the date is not a "VarChar" and it is a "Date" type (as it should be) then this technique will not work because you shouldn't be passing a string into the database for the update command: you should be passing a Date into the database for the update command. The database will then store the Date in the column and it will format the date the way that it's configured to format dates.

-Frinny

[Frinavale]

So upon perusing the forums here on bytes I came across an answer (sort of) to your question.

Your question about how to change the format of a date stored in a database isn't a VB.NET question at all!

It's actually a database question.
You need to change the type (or configuration) of date that you are storing in the database through the database management system that you are using.

Like this How to convert a date from yyyy-mm-dd to dd-mm-yyyy in a db2 database.

Hope I'm making sense.

-Frinny