如何在数据表中的数据列中格式化日期时间短字符串格式 [英] How to format datetimeshortstring format in datacolumn in datatable

问题描述

如何格式化数据表中数据列的短日期字符串.数据在数据表中，PlannedEndDate 应该格式化为shortdatestring.

How to format shortdatestring for datacolumn in datatable. The data is there in datatable, PlannedEndDate Should be formatted to shortdatestring.

请推荐

Private Function ReadSheetToDatatable(ByVal sheetName As String) As DataTable
            Dim dt As DataTable = New DataTable(sheetName)
            dt.Columns.Add(New DataColumn("InquiryNumber", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("Description", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("TagNo", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("TagDescription", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("MileStone", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("SubMilestone", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("ActivityID", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("PlannedStartDate", System.Type.GetType("System.String")))
            dt.Columns.Add(New DataColumn("PlannedEndDate", System.Type.GetType("System.String")))
            'dt.Columns.Add(New DataColumn("PlannedStartDate", System.Type.GetType("System.DateTime")))
            'dt.Columns.Add(New DataColumn("PlannedEndDate", System.Type.GetType("System.DateTime")))

            Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection(GetConnectionString())
                If (Not IsNothing(con)) Then
                    Try
                        'InquiryNumber  Description TagNo   TagDescription  MileStone   SubMilestone    ActivityID  PlannedStartDate PlannedEndDate
                        Dim strSql As String = String.Format("SELECT InquiryNumber, Description, TagNo, TagDescription, MileStone, SubMilestone, ActivityID, PlannedStartDate, PlannedEndDate FROM [{0}]", sheetName)
                        'Dim strSql As String = String.Format("SELECT * FROM [{0}]", sheetName)
                        Dim dataAdapter As New OleDbDataAdapter()
                        dataAdapter.SelectCommand = New OleDbCommand(strSql, con)
                        dataAdapter.Fill(dt)

                        Return dt
                    Catch ex As Exception
                        'PRABHU 25-NOV-2012 used by another process issue fix
                        Petrofac.PTR.Framework.Exceptions.SaveDBExceptionLog(ex.Message, Err.Number, ex.StackTrace.ToString(), "ExcelService", Petrofac.PTR.Framework.SecurityManager.GetBadgeNo())
                        _statusMessage = String.Format("Reading {0} failed ...", sheetName.Replace("$", ""))
                        Return Nothing
                    Finally
                        con.Dispose()
                    End Try
                End If
            End Using
            _statusMessage = String.Format("Reading File failed ...")
            Return Nothing
        End Function

推荐答案

像这样使用查询

SELECT InquiryNumber, Description, TagNo, TagDescription, MileStone, 
SubMilestone,ActivityID, PlannedStartDate, 
FORMAT(PlannedEndDate ,'dd/MM/yyyy')FROM [{0}]

这篇关于如何在数据表中的数据列中格式化日期时间短字符串格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！