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.
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
Re: Convert from string to date
Quote:
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
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
Re: Convert from string to date
Quote:
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
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.
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.
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.