none
Why saving worng date in sqldatabase RRS feed

  • Question

  • i am saving date in custom fromat dd/MM/yyyy from datetimepicker propertis. but it is saving MM/dd/yyyy in database.it is saving wrong values of data and month i can't understand why? but when i save the date as 2018/03/15, it is saved as 2018/15/03.when i want to see a report, it works when i enter day as month and month as day.how can i solve this problem.Please help

    What I have tried:

    changed date customformat many times in datetimepicker propertis.but it did not work
    Monday, April 9, 2018 8:27 AM

All replies

  • Hello,

    Dates are stored in a database according to the locale and formatting is the responsibility of the developer when retrieving data out of the database table. Similarly, you can't store a currency symbol for a decimal type in a database, data is stored raw and the formatting is the developer's responsibility.

    Here is an example for SQL-Server, the first shows Birthday as saved in the database while the second and third are custom formatted on the exact same column. 

    So for a VB.NET app, you do the formatting in the control which displays the dates e.g. DateTimePicker, DataGridView etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Monday, April 9, 2018 9:10 AM
    Moderator
  • i am using c# to insert date values in sqlserver database.


    i don't understand why date is saving in wrong format while my datetimepicker shows right date.
    Monday, April 9, 2018 9:25 AM
  • i am using c# to insert date values in sqlserver database.


    i don't understand why date is saving in wrong format while my datetimepicker shows right date.

    I just explained this to you, you don't save formatting in a database table.

    An extreme measure is altering the locale of the server which means all databases are affected,

    ALTER LOGIN your_login WITH DEFAULT_LANGUAGE=British
    But the date storage is different from what is displayed in your app.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, April 9, 2018 9:39 AM
    Moderator
  • If you are saving the date in a column that is a Date or DateTime data type then it is *not* stored with a format - it is stored as a numeric value. You can format the date once it has be retrieved from the database when you are displaying in the UI.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 9, 2018 12:24 PM
  • i am using c# to insert date values in sqlserver database.


    i don't understand why date is saving in wrong format while my datetimepicker shows right date.

    DateTimePickers can be set for Custom Format display too.

    As long as you store the DateTime as it comes from DateTime and not in custom format it can be retrieved and converted to DateTime again then use custom formatting with it for display. If you store the DateTime in custom format then it is not accurate format anymore to what the actual DateTime retrieves for a current date.

    Just store the DateTime in normal format and then it will display properly for whatever current culture or custom format you want to use with it.


    La vida loca

    Monday, April 9, 2018 11:33 PM
  • Hi Rana,

    Why did you want to save dd/MM/yyyy date formate in sql database, I think it doesn't matter if you save any date format date, because you can convert this date into any date format you like in sql query.

    If you must do this, you can set date column type as varchar in sql database, then like this:

     Dim str As String = "Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testlocaldb;Integrated Security=True"
            Dim sql As String = "insert into test3 values (@firstname,@lastname,@birthday,@birthday1)"
            Using con As New SqlConnection(str)
                con.Open()
                Using cmd As New SqlCommand(sql, con)
                    cmd.Parameters.AddWithValue("@firstname", "Cherry2")
                    cmd.Parameters.AddWithValue("@lastname", "bu2")
                    cmd.Parameters.AddWithValue("@birthday", DateTimePicker1.Value)
                    cmd.Parameters.AddWithValue("@birthday1", Format(DateTimePicker1.Value, "dd/MM/yyyy"))
                    If cmd.ExecuteNonQuery Then
                        MessageBox.Show("insert successfully!")
                    End If
                End Using
            End Using

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Thursday, April 19, 2018 9:34 AM
    Tuesday, April 10, 2018 3:22 AM
    Moderator