CODEWITHSUNDEEP
sql-servervb.netwinforms
Gidil
Gidil

Reputation: 4137

How to set date format from VB.NET form

I have a VB.NET form that passes the users choices to my database (SQL Server 2008). One of the values the user chooses is a date from a date picker object. This date is stored in the database as a string.
I've noticed that on different deployments the date format inserted into the database is different and that it is dependent on the data format set on the server.

This can be a real problem for me since I need to install our process on several servers (for different clients) and I don't necessarily have the option of changing the servers date format.

Is there a way to programmaticly define the date format for the output to the database?

Upvotes: 3

Views: 37656

Answers (6)

Saif ali Karedia
Saif ali Karedia

Reputation: 912

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    Dim strtdate As String
    Dim enddate As String

    strtdate = DateTimePicker1.Value.ToString("yyyy-MM-dd")
    enddate = DateTimePicker2.Value.ToString("yyyy-MM-dd")
    'MsgBox(strtdate)
    Using con As New SqlConnection(connectionString)
        Using cmd As SqlCommand = con.CreateCommand()
            cmd.CommandText = "SELECT * FROM CleanData where Notice_Date between @noticestartdate and @noticeenddate order by Notice_Date"
            cmd.Parameters.AddWithValue("@noticestartdate", strtdate)
            cmd.Parameters.AddWithValue("@noticeenddate", enddate)
            cmd.CommandType = CommandType.Text
            Using sda As New SqlDataAdapter(cmd)
                Using dt As New DataTable()
                    sda.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
            End Using
        End Using
    End Using
End Sub

Upvotes: 0

SMHasnain
SMHasnain

Reputation: 706

correct me if I am wrong, you want to set the format of datetimepicker constant.

Here is the code.

Me.DateTimePicker1.Format = DateTimePickerFormat.Custom
Me.DateTimePicker1.CustomFormat = "dd/MM/yyyy"

You can set it for Hours and Seconds also.

output:

30/08/2013

It will always be in the same format you save it to the database or retrieve it.

Upvotes: 0

jmoreno
jmoreno

Reputation: 13571

I believe the best answer to your question is: don't do that. Dates in a database should be dates, date times should be datetimes, integers should be ints or bigint and so forth.

I can't think of a even a lousy reason to store a date as a string, let alone a good one.

If you are storing dates as a string, stop doing that. If you are storing the dates as dates, but passing in a string, stop doing that too -- switch to sql parameters.

Upvotes: 1

Nicholas Post
Nicholas Post

Reputation: 1857

If you are storing the date & time information to SQL using the DateTime field type, then SQL should automatically convert it to the format in which it uses to store those values (YYYY-MM-DD hh:mm:ss).

If, on the other hand, you are storing them as text (varchar, etc.), then you would have to modify your code to pass the date & time the way you want it.

Perhaps something like:

Dim strDate as string = Date.Now.ToString("MM/dd/yyyy hh:mm:ss tt") 'Returns date with AM/PM'

Dim strDate as string = Date.Now.ToString("MM/dd/yyyy HHH:mm:ss") 'Returns date with military time'

Upvotes: 3

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

The date formats in the database are not stored in the way they may be presented to you in Management Studio or other client tools (they may apply regional formatting due to your locale or other settings).

If you are storing date/time values in the database, your data type should be DATE, DATETIME, SMALLDATETIME, etc., not a string type like VARCHAR or NVARCHAR. When you store dates as strings you lose all kinds of capabilities: automatic validation (junk prevention), date/time math, efficient date range queries, etc. If you stuff 01/06/2013 in a string column, how can anyone ever know if you meant January 6th or June 1st? How do you expect to sort such a string?

Your VB.NET form should pass a date time parameter and format should never be a part of it. A date/time value should never be treated as a string except when you need to present it to the user, in which case you should use .Format() or .ToString() in the presentation tier.

If you absolutely must pass a string, make sure it is in an absolutely unambiguous format, such as the following for datetime:

YYYYMMDD hh:mm:ss.nnn
YYYY-MM-DDThh:mm.ss.nnn

Other formats can be interpreted incorrectly depending on language, dateformat and other settings. But you shouldn't need to worry about that because you should be passing date/time values as date/time parameters, not strings.

Upvotes: 2

ps2goat
ps2goat

Reputation: 8485

Date formats in the database are set in the database configuration.

When you query your data, you can format the dates as shown here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Also, look into this: http://technet.microsoft.com/en-us/library/ms189491.aspx

Upvotes: -1

Related Questions