Convert "dd/MM/yyyy" to "MM/dd/yyyy"

Question

I have a problem with inserting date into a database and displaying it.
I use Visual Basic 2013 with Microsoft SQL database file.
I have a table called Appointments, there is a column called Date with a type of date.
I have a form with a DateTimePicker displaying date in this format "dd/MM/yyyy" my computer regional settings.

When I try to insert it into the database, by obtaining the date from the DateTimePicker as follows:

DateTimePicker1.Value.Date

I still get an error. I know that Microsoft SQL only takes this format "MM/dd/yyyy", but is there any way to convert from "dd/MM/yyyy" to "MM/dd/yyyy" using visual basic and sql?

Dim con As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=" + My.Settings.strTextbox + ";Integrated Security=True;Connect Timeout=30")
        Dim cmd As New SqlCommand("insert into Appointment (AssignDoc,Date,TimeFrom,FileNo) values (@AssignDoc,@Date,@TimeFrom,@FileNo)")
        cmd.Connection = con
        con.Open()
        cmd.Parameters.AddWithValue("@AssignDoc", ComboBox6.SelectedItem)
        cmd.Parameters.AddWithValue("@Date", DateTimePicker1.Value.Date)
        cmd.Parameters.AddWithValue("@TimeFrom", ComboBox1.SelectedItem)
        cmd.Parameters.AddWithValue("@FileNo", TextBox2.Text)
        Dim reader As SqlDataReader = Nothing
        Try
            cmd.ExecuteNonQuery()
            con.Close()
            'CreateAppointment()
            MsgBox("Appointment Created Successfully", MsgBoxStyle.Information, "Appointment Created")
            TextBox1.Text = ""
            TextBox2.Text = ""
        Catch ex As System.Exception
            MsgBox(ex.Message)
        End Try

Dates do not have a format. The DatePicker is simply displaying it in a given format which sounds like is in a different culture. — Ňɏssa Pøngjǣrdenlarp, Jan 03 '15 at 02:04
MS SQL can take any format. What's the code and what is the error? — Nick.Mc, Jan 03 '15 at 03:45
Sorry, I added the code, it inserts this format in the database "dd/MM/yyyy" I want it to be stored in the database using this format "MM/dd/yyyy" — MAX, Jan 03 '15 at 09:44
`Microsoft SQL only takes this format "MM/dd/yyyy"` This is not true. You can input the dates in many different formats. For your reference: http://www.sql-server-helper.com/tips/date-formats.aspx. I suggest you use the ISO format: **YYYY-MM-DD** — Phantômaxx, Jan 03 '15 at 11:02

Drunken Code Monkey · Answer 1 · 2017-05-26T00:55:02.837

1

Use a command object in ADO.NET to pass queries, and use its mechanism to insert parameters. Passing your date as a string is the source of your problem. You cannot guarantee that the SQL Server database will be configured for a collation compatible with your hardcoded date format.

See How to pass a parameter from vb.net

edited May 26 '17 at 00:55

answered Jan 03 '15 at 03:50

Drunken Code Monkey

1,796
1
14
18

Hitesh · Accepted Answer · 2015-01-03T12:07:09.207

0

Your statement I know that Microsoft SQL only takes this format "MM/dd/yyyy" itself is wrong as SQL takes dates in any of the specified formats here

You just need to convert them in that format before entering it into the database.

For e.g. if you are passing the parameter in "mm/dd/yyyy" format, you need to convert that in sql as

insert into Appointments (Date) values (convert(date,@parameter,101));

http://www.sqlfiddle.com/#!3/65cdd/1/0

edited Jan 03 '15 at 12:07

answered Jan 03 '15 at 05:17

Hitesh

3,449
8
39
57

My DateTimePicker displays this format "dd/MM/yyyy" but I want it in this format "MM/dd/yyyy" stored in the database. I did this and still no luck, stores in same format as my DateTimePicker ......... Dim cmd As New SqlCommand("insert into Appointment (AssignDoc,Date,TimeFrom,FileNo) values (@AssignDoc,convert(date,@Date,101),@TimeFrom,@FileNo)") – MAX Jan 03 '15 at 09:52
No matter what format you pass to SQL, it will save in table in the standard format only. You have to write query while inserting or retrieving to change formats accordingly. Since now you are saving it in MM/dd/YYYY format, while retrieving, write it as `convert(varchar(50),Date,101)` and it will give you output in MM/dd/YYYY format – Hitesh Jan 03 '15 at 10:00
Sorry I'm confused, my select statement retrieves the Date where the Date bigger than today's date. where should I put convert(varchar(50),Date,101) in my select statement. – MAX Jan 03 '15 at 10:39
select convert(varchar(50),Date,101) from Appointments where Date > GetDate() – Hitesh Jan 03 '15 at 11:03
not working! doesn't show dates greater than today's date, I don't know why should I convert it to varchar when it is stored in the database as date. I want to compare two dates together??? thanks in advance. – MAX Jan 03 '15 at 11:58
I have made a fiddle for you, please check it in my edited answer, hope that helps you – Hitesh Jan 03 '15 at 12:07
Thanks a lot, it works now, your fiddle helped me understand the concept. – MAX Jan 03 '15 at 14:07
It is not necessary to convert the date at all. Instead, use a SqlParameter object and specify the type and it should "just work". @MAX the code you posted seems ok. Please provide the actual error message you are getting. – Chris Dunaway Jan 05 '15 at 22:08

Convert "dd/MM/yyyy" to "MM/dd/yyyy"

2 Answers2