sql-server - 如何从 Excel 记录集中格式化日期?
问题描述
我从 SQL Server 获取数据,现在我想将其复制到 Excel 工作表中。
我尝试格式化“start_date”,但它仅在第一列上应用该格式,所有其他列都显示为存储在 SQL Server 中。
Sub ReadMBDataFromSQL()
Dim Server_Name, Database_Name, User_ID, Password, SQLStr As String
Set Cn = CreateObject("ADODB.Connection")
Set RS = New ADODB.Recordset
Server_Name = ""
Database_Name = ""
User_ID = ""
Password = ""
SQLStr = "SELECT lot, po, start_date, input_sponge_type, input_sponge_type FROM PalladiumNitrateMB WHERE start_date >= '" & Format(Range("start"), "mm-dd-yyyy") & "' AND start_date < '" & Format(Range("end"), "mm-dd-yyyy") & "' ORDER BY lot ASC"
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
RS.Open SQLStr, Cn, adOpenKeyset, adLockBatchOptimistic
With Worksheets("PdNitrateMassBalance").Range("A5:N600")
RS("start_date") = Format(RS("start_date"), "dd/mm/yyyy")
.ClearContents
.CopyFromRecordset RS
End With
RS.Close
Set RS = Nothing
Cn.Close
Set Cn = Nothing
End Sub
解决方案
尝试更换这部分
With Worksheets("PdNitrateMassBalance").Range("A5:N600")
RS("start_date") = Format(RS("start_date"), "dd/mm/yyyy")
.ClearContents
.CopyFromRecordset RS
End With
有了这个
With Worksheets("PdNitrateMassBalance")
With .Range("A5:N600")
.ClearContents
.CopyFromRecordset RS
End With
FixDatesFromYYYY_MM_DD .Range("A:A"), "dd/mm/yyyy"
End With
和额外的子:
Sub FixDatesFromYYYY_MM_DD(DatesRange As Range, Format As String)
Dim r As Range
Dim firstDash As Integer, secondDash As Integer, i As Integer
For Each r In DatesRange
If Not r.Value = "" Then
firstDash = 0
secondDash = 0
For i = 1 To Len(r.Text)
If Mid(r.Text, i, 1) = "-" Then
If Not firstDash = 0 Then
secondDash = i
Exit For
Else
firstDash = i
End If
End If
Next
With r
.Value = DateSerial(Left(r.Text, 4), Mid(r.Text, firstDash + 1, IIf(secondDash = 7, 1, 2)), Mid(r.Text, secondDash + 1))
.NumberFormat = Format
End With
End If
Next
End Sub
更新
添加了一个从“yyyy-mm-dd”文本格式转换日期的子。
推荐阅读
- tensorflow - 我应该将分类输出转换为整数吗?如何转换?
- java - 如何将 2 个 @GetMapping 注释映射到同一个 URL
- python - 子列表每个位置的正则表达式模式的频率
- java - 为什么 crud 存储库存在(2 个值)发现不区分大小写而不是区分大小写?
- vim - 如何从 Vim 命令行复制文本
- android - 当前用户禁用托管令牌
- javascript - 如何在子菜单中将 SemanticUIReact 图标移动到文本元素的左侧?
- c++ - 适用于 Windows 10 (x64) 的 tesseract.lib(版本 4)
- c# - 使用委托包装异常处理时,Web-API 调用不起作用
- c# - 如何编写发送电子邮件的简单代码