0

My Table sample has 3 columns: id (int), name (varchar), date_surrendered (date).

I have a DateTimePicker; when I choose October 8, 2018 and save it, it will save as YYYY-dd-MM in SQL Server. Just like this:

id | name | date_surrendered

1 | noe | 2018-08-10

When I search about Date format, there's no format like "YYYY-dd-MM"
Date and Time Conversions Using SQL Server

Form1:

 Private Sub add_ClickS(sender As Object, e As EventArgs) Handles add.Click
    If Sql.HasConnection() = True Then
        Sql.Save(namee.Text, datesurrendered.Value)
        GridRefresh()
    End If
End Sub

Conn.vb

Public Sub Save(a, b)
     Try
         SqlCon.Open()
         SqlCommand = New SqlCommand("INSERT INTO sample (name, date_surrendered) VALUES (
                                      @name,@datesurrendered)", SqlCon)

         SqlCommand.Parameters.Add("@name", SqlDbType.Text).Value = a
         SqlCommand.Parameters.Add("@datesurrendered", SqlDbType.DateTime).Value = b

         SqlAdapter = New SqlDataAdapter(SqlCommand)
         SqlDataset = New DataSet
         SqlAdapter.Fill(SqlDataset)
         SqlCon.Close()
     Catch ex As Exception
         MsgBox(ex.Message)
        SqlCon.Close()
    End Try
 End Sub
Jimi
  • 29,621
  • 8
  • 43
  • 61
Heartless Vayne
  • 904
  • 1
  • 8
  • 18
  • Don't concatenate strings to buuild your sql query, parameters should **always** be passed as sql-parameter not as string. Otherwise you are vulnerable to sql injection and you have issues like this. – Tim Schmelter Oct 07 '18 at 15:16
  • Okay sir, I will update you Sir. – Heartless Vayne Oct 07 '18 at 15:20
  • The `Value` of a `DateTimePicker` is a `DateTime`, not a `String`. If you create your command object properly, i.e. using parameters, then there will never be a conversion from `DateTime` to `String` so format is never a consideration. Now that you are using parameters, your issue has gone away. That is just one of the several reasons to ALWAYS use parameters. – jmcilhinney Oct 07 '18 at 15:53
  • 1
    By the way, if your database column is type `date` then your parameter should be type `SqlDbType.Date`, not `SqlDbType.DateTime`. Also, you should not be using `SqlDbType.Text` unless your database column is type `text` and no database column should be that type these days. Your database column should be type `varchar` and you should be using `SqlDbType.VarChar` and specifying the length for your parameter. – jmcilhinney Oct 07 '18 at 15:56
  • 1
    Also, the date is not saved to the database with any format. If your column is type `date` then it is simply a number. SQL Server displays dates in the format "yyyy-MM-dd" because it is unambiguous. Most people would generally use "dd-MM-yyyy" or "MM-dd-yyyy" but many values in those formats are ambiguous so it is not safe to use those formats in a generic environment. – jmcilhinney Oct 07 '18 at 16:01
  • Problem solved sir :) thanks – Heartless Vayne Oct 07 '18 at 16:21
  • You should also assign this command as `InsertCommand` to the DataAdapter not as SelectCommand, what you did by using the constructor that takes a `SqlCommand`: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter.-ctor?view=netframework-4.7.2#System_Data_SqlClient_SqlDataAdapter__ctor_System_Data_SqlClient_SqlCommand_ – Tim Schmelter Oct 07 '18 at 16:23
  • [Dates don't have a *display format* in SQL Server](https://stackoverflow.com/a/30033028/3094533) (and neither does the .Net `DateTime` struct). – Zohar Peled Oct 07 '18 at 19:12

0 Answers0