convert dd/MM/yyyy hh:mm:ss to dd.MM.yyyy in asp.net programaticaly

HI,

I get data from sql table to dataset and then to gridview. Wnen I add data to table date format for Service_date is dd/MM/yyyy hh:mm:ss I want to convert this format to dd.MM.yyyy so I can have date in this format loaded in gridview, but I dont know how, what ever I tried I always have this format with time. In my web.config I have:

    <globalization
       requestEncoding="utf-8"
       responseEncoding="utf-8"
       culture="en-GB"
       uiCulture="en-GB" />

and this is code behind for page
Imports System.Data.SqlClient

Partial Class book
    Inherits System.Web.UI.Page
    Dim kb As New kBazica

    Private Function CreateDataTable() As Data.DataTable
        Try
            Dim myDataTable As Data.DataTable = New Data.DataTable()
            Dim myDataColumn As Data.DataColumn

            myDataColumn = New Data.DataColumn()
            myDataColumn.DataType = Type.GetType("System.String")
            myDataColumn.ColumnName = "SystemID"
            myDataTable.Columns.Add(myDataColumn)

            myDataColumn = New Data.DataColumn()
            myDataColumn.DataType = Type.GetType("System.String")
            myDataColumn.ColumnName = "Name"
            myDataTable.Columns.Add(myDataColumn)

            myDataColumn = New Data.DataColumn()
            myDataColumn.DataType = Type.GetType("System.String")
            myDataColumn.ColumnName = "Service_date"
            myDataTable.Columns.Add(myDataColumn)

            myDataColumn = New Data.DataColumn()
            myDataColumn.DataType = Type.GetType("System.String")
            myDataColumn.ColumnName = "Service_description"
            myDataTable.Columns.Add(myDataColumn)


            Return myDataTable
        Catch ex As Exception

        End Try

    End Function

    Private Function AddDataToTable(ByVal SystemID As String, ByVal Name As String, ByVal Service_date As String, ByVal Service_description As String, ByVal myTable As Data.DataTable)

        Try
            Dim row As Data.DataRow = myTable.NewRow()
            row("SystemID") = SystemID
            row("Name") = Name
            row("Service_date") = Service_date
            row("Service_description") = Service_description

            myTable.Rows.Add(row)
            myTable.AcceptChanges()
        Catch ex As Exception

        End Try
    End Function

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim sesid As String
        Dim SystemID As String
        Dim Name As String
        Dim Service_date As DateTime
        Dim Service_description As String
        Dim ds As New Data.DataSet
        sesid = Session("SysID").ToString

        ds = kb.SQLpuni("SELECT SystemID,Name,Service_date,Service_description from Servers_Service_Book WHERE SystemID='" + sesid + "' ORDER BY Service_date DESC;", ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)


        If ds.Tables(0).Rows.Count > 0 Then
            For Each dr As Data.DataRow In ds.Tables(0).Rows
                SystemID = dr.Item("SystemID")
                Name = dr.Item("Name")
                Service_date = dr.Item("Service_date")
                Service_description = dr.Item("Service_description")

            Next
        End If

        Me.GridView1.DataSource = ds
        Me.GridView1.AutoGenerateColumns = True
        Me.GridView1.DataBind()

        GridView1.SelectedIndex = -1

    End Sub

Open in new window

LVL 1
kahvedzicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

robastaCommented:
Change Line 72 to:

Service_date =Format( dr.Item("Service_date"),"dd/MM/yyyy")



masterpassCommented:
use the dataformat string

<asp:BoundField
    DataField="BirthDate"
    DataFormatString="{0:dd.MM.yyyy}"
    HtmlEncode="false"
    HeaderText="Birth date" />

Impt: you have to use HtmlEncode="false" if this should work !!!
samsymonCommented:
Function Datestring(ByVal DateValue As DateTime) As String
        Dim getformat As String
        getformat = DateValue.Day.ToString.PadLeft(2, "0") & "." & DateValue.Month & "." & DateValue.Year
        If getformat.Length > 0 Then
            Return getformat
        Else
            Return ""
        End If
    End Function


-----------

Service_date =Datestring( dr.Item("Service_date"))

hope this will solve the problem.

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

samsymonCommented:
sorry a little mistake

row("Service_date") = Datestring(Service_date)
AbarajCommented:
Service_date = dr.Item("Service_date") .ToString("dd.MM.yyyy");
kahvedzicAuthor Commented:
@masterpass
this is working but this way I have to create BoundFields in gridview that are bound with columns in data table. Is there a way to convert date format in code behind without creating BoundFields? This way I have to set AutoGenerateColumns to False.

@robasta
This
Service_date =Format( dr.Item("Service_date"),"dd/MM/yyyy")
is not working, still have same format in gridview.
robastaCommented:
Service_date =Format( dr.Item("Service_date"),"dd.MM.yyyy")

This portion "dd.MM.yyyy" specifies the required format. you can change it to your required format. (replaced the slashes with '.')

kahvedzicAuthor Commented:
@samsymon

with this function I still have same format in gridview.

@Abaraj
Value of type 'Char' cannot be converted to 'Date' is the message that I have with this code.
kahvedzicAuthor Commented:
I did that but nothing changed in gridview.
AbarajCommented:
Service_date = Convert.ToDateTime(dr.Item("Service_date") .ToString("dd.MM.yyyy"));
kahvedzicAuthor Commented:
@Abaraj

Conversion from string "dd.MM.yyyy" to type 'Integer' is not valid.
AbarajCommented:
in AddDataToTable() method,
Dim Sr_date As DateTime
Sr_date =  Convert.ToDateTime(Service_date)
Service_date = Sr_date.ToString("dd.MM.yyyy")
robastaCommented:
In this comment: ID:31189125
you asked: Is there a way to convert date format in code behind without  creating BoundFields?

The answer :

You 'do not' have access to the DataFormatString property at runtime. If you do not want to set the DataFormatString at design time, you would need to override
CreateAutoGeneratedColumns(PagedDataSource dataSource) and/or CreateAutoGeneratedColumn(AutoGeneratedFieldProperties fieldProperties) methods to provide AutoGeneratedField with
DataFormatString set as needed.

take a look here: http://forums.asp.net/t/888287.aspx
maheshsnkrCommented:
hi kahvedzic,

My suggestion is written below....

declare 'Service_date' as string
in line 72:
Make the code as

Service_date = dr.Item("Service_date").ToString("dd.MM.yyyy")

samsymonCommented:
change the code  myDataColumn.DataType = Type.GetType("System.String")

the problem is here.

change it to the date format with the required format.
drypzCommented:
Hi! Why don't you just format the value during databound event in your gridview.Something like

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            'assuming the value you want to format is in cell 0
            Dim formattedText As String = Convert.ToDateTime(e.Row.Cells(0).Text).ToString("dd.MM.yyyy")
            e.Row.Cells(0).Text = formattedText

        End If
    End Sub
chrisgreavesCommented:
This can also be done inside the SQL returning the data:
SELECT   item1, item2, CONVERT(varchar, ServiceDate, 103) as [Service Date], item4 etc
FROM mytable

Chris
Mark WillsTopic AdvisorCommented:
good idea Chris... assuming it is a SQL database...

just one small addition, and that is use the replace function to change from "/" to "."  e.g. select replace(CONVERT(char(10), ServiceDate, 103),'/','.') as ServiceDate

So, line 65 might become :

ds = kb.SQLpuni("SELECT SystemID,Name,replace(CONVERT(char(10), Service_Date, 103),'/','.') as Service_date,Service_description from Servers_Service_Book WHERE SystemID='" + sesid + "' ORDER BY Service_date DESC;", ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kahvedzicAuthor Commented:
Thanks guys for all effort.

Cheers
chrisgreavesCommented:
Excellent! I didn't know you could do that...that is why you are a master!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.