Changing date format from date field in database.

Question

I am using ASP.NET with SQL Server 2005.

My date get saved in the database with data type = "smalldatetime".

I am displaying the date value into a text box called txtDate with a Reader......

    Dim conn As Data.SqlClient.SqlConnection
    Dim Comm As Data.SqlClient.SqlCommand
    Dim reader As Data.SqlClient.SqlDataReader

    conn = New Data.SqlClient.SqlConnection(..............)

    Comm = New Data.SqlClient.SqlCommand( _
    ("SELECT * FROM Table1 WHERE Age = 88"), conn)

    conn.Open()

    reader = Comm.ExecuteReader()

    If reader.Read() Then

        txtDate.Text = reader.Item("theDate").ToString

    End If

    reader.Close()
    conn.Close()

But currently my date get displayed as "2008/06/30 12:00:00 AM" and i want to display it as "30 June 2008".

How can this be done?

Answer 1

Replace ToString with ToString("dd MMMM yyyy"). You might want to look in MSDN at the overloads for DateTime.ToString, and at date-time formatting strings.

Other points with your code - if you really are selecting a single value from a single row, then restrict your select list (i.e. don't use SELECT *) and consider using ExecuteScalar rather than ExecuteReader...

Answer 2

How the data is stored in the database is irrelevant - you'll be getting a DateTime back in your client code. You then just need to format that however you want.

Try something like this:

If reader.Read() Then
    Dim date as DateTime = CType(reader.Item("theDate"), DateTime)
    txtDate.Text = date.ToString("dd MMMM yyyy")
End If

Here the "dd" means "day of month", "MMMM" means "month of year in full" and "yyyy" means "4-digit year".

See MSDN on custom date and time format strings for more details. Alternatively, you could use a standard date and time format string which would take account of your current culture. (The custom ones will take account of that for each individual field, so month names will be appropriate to the culture, but the order and style of the fields to be formatted is fixed by the format string.)

Answer 3

You should use the data format to string.

Cast your value reader.Item("theDate") to a DateTime object and the use the ToString Method.

yourDateTime.ToString("dd MMMM yyyy");

Have a look at these

• Date and Time Format Strings
• DateTime.ToString() Patterns