sql where 日期 = ‘2023/6/1’没通过。

ning84 · 发表于 2023-10-6 09:29

最简单的SQL,怎么没通过。
Str = "Select 日期 From [TraverseFolderFile$A1:Z47627] Weree 日期 = '#2023/6/1#'"
Str = "Select 日期 From [TraverseFolderFile$A1:Z47627] Weree format(日期,'yyyy/m/d') = format('2023/6/1','yyyy/m/d') "

f8b1987 · 发表于 2023-10-6 09:51

本帖最后由 f8b1987 于 2023-10-6 13:43 编辑

日期不用加引号，这个带时间的，最好是取整

笑着...两年过去 · 发表于 2023-10-6 09:52

日期 >= '#2023/6/1#' and 日期 < '#2023/7/1#'"

ning84 · 发表于 2023-10-6 13:15

f8b1987 发表于 2023-10-6 09:51
日期不用加引号，这个带时间的，最好是取整

谢谢回复，下面语句通过，但格式转换比骄麻烦。

Select * From [TraverseFolderFile$A1:A47627] Where 日期 = 45078.4746990741

麻烦给个例子，加深理解。

下面语句没有通过。

Str = "Select * From [TraverseFolderFile$] Weree 日期 >= '#2023/6/1#' and 日期 < '#2023/7/1#'"
Str = "Select 日期 From [TraverseFolderFile$A1:A47627] Weree 日期 >= '#2023/6/1#' and 日期 < '#2023/7/1#'"
Str = "Select 日期 From [TraverseFolderFile$A1:A47627] Weree 日期 = '#2023/6/1#' "
Str = "Select * From [TraverseFolderFile$A1:A47627] Weree 日期 >= '#2023/6/1#' and 日期 < '#2023/7/1#'"

f8b1987 · 发表于 2023-10-6 13:43

参考
' 构建查询语句
strSQL = "SELECT * FROM [SS$A1:A23] WHERE [日期时间] > #2023-09-05# AND [日期时间] < #2023-09-10#"

limonet · 发表于 2023-10-6 16:20

"select * From [Sheet1$A:A] where int(日期)=#2023-10-06#"

ning84 · 发表于 2023-10-6 18:44

limonet 发表于 2023-10-6 16:20
"select * From [Sheet1$A:A] where int(日期)=#2023-10-06#"

谢谢回复

Function MergeDate(Rs As Recordset, Sht As Worksheet)
Dim Rng As Range
With Sht
Set Rng = .Cells(.Cells(65536, 2).End(xlUp).Row + 2, 1)
Debug.Print Rng.Address
Rng.CopyFromRecordset Rs
Set Rng = Rng.Resize(Rs.RecordCount + 0, 1)
Rng.Select
Rng.Merge
Set Rng = Rng.CurrentRegion
Rng.Select
''
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
'''
End With
End Function
Private Sub dSqlCreateFolder()
Application.DisplayAlerts = False
Dim oFolder As Folder, oFile As File, oFile1 As File
Dim Fso As Scripting.FileSystemObject
Set Fso = New Scripting.FileSystemObject
Dim Sht As Worksheet
Set Sht = Sheet4
Set Sht = Sheets("TraverseFolderFile")
Sht.Activate
Dim Rng As Range, oRng As Range
Set Rng = Sht.Range("A1:Z" & Sht.Cells(65536, 1).End(xlUp).Row + 10)
Debug.Print Rng.Address
Dim Rs As Recordset, Rs1 As Recordset, Str
Dim oPath, oPath1
Dim oDate As Date
''
With Sheet2
.Activate
.Cells.Clear
.Cells.Font.Size = 9
''
Str = "Select Distinct Format(日期,'yyyy-mm')" & _
" From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] "
'Debug.Print Str
''
Set Rs = SqlRetuRs(Str)
.Cells(2, 20).CopyFromRecordset Rs
Rs.MoveFirst
For ii = 0 To Rs.RecordCount - 1
If IsDate(Rs.Fields(0)) Then
oDate = Rs.Fields(0)
Str = "Select Format(日期,'yyyy-mm'), Format(日期,'yyyy-mm-dd')" & _
" From [TraverseFolderFile$A1:Z47627] Where 日期 >= #" & Format(oDate, "yyyy/mm/dd") & "# and 日期 < #" & Format(oDate + 1, "yyyy/mm/dd") & "#"
Str = "Select Format(日期,'yyyy-mm'), Format(日期,'yyyy-mm-dd')" & _
" From [TraverseFolderFile$A1:Z47627] Where 日期 >= #" & Format(oDate, "yyyy/mm") & "# and 日期 < #" & Format(oDate + 1, "yyyy/mm") & "#"
Str = "Select distinct Format(日期,'yyyy年mm月'), Format(日期,'yyyy年mm月dd日')" & _
" From [TraverseFolderFile$A1:Z47627] Where 日期 >= #" & Format(oDate, "yyyy/mm") & "# and 日期 < #" & Format(oDate + 32, "yyyy/mm") & "#"
Set Rs1 = SqlRetuRs(Str)
If Rs1.RecordCount > 0 Then
MergeDate Rs1, Sheet2
End If
End If
Rs.MoveNext
Next ii
End With
Application.DisplayAlerts = True
End Sub

复制代码

ning84 · 发表于 2023-10-6 19:03

limonet 发表于 2023-10-6 16:20
"select * From [Sheet1$A:A] where int(日期)=#2023-10-06#"

谢谢回复，各位的回复。

Function MergeDate(Rs As Recordset, Sht As Worksheet)
Dim Rng As Range
With Sht
Set Rng = .Cells(.Cells(65536, 2).End(xlUp).Row + 2, 1)
Debug.Print Rng.Address
Rng.CopyFromRecordset Rs
Set Rng = Rng.Resize(Rs.RecordCount + 0, 1)
Rng.Select
Rng.Merge
Set Rng = Rng.CurrentRegion
Rng.Select
''
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
'''
End With
End Function
Private Sub dSqlCreateFolder()
Application.DisplayAlerts = False
Dim oFolder As Folder, oFile As File, oFile1 As File
Dim Fso As Scripting.FileSystemObject
Set Fso = New Scripting.FileSystemObject
Dim Sht As Worksheet
Set Sht = Sheet4
Set Sht = Sheets("TraverseFolderFile")
Sht.Activate
Dim Rng As Range, oRng As Range
Set Rng = Sht.Range("A1:Z" & Sht.Cells(65536, 1).End(xlUp).Row + 10)
Debug.Print Rng.Address
Dim Rs As Recordset, Rs1 As Recordset, Str
Dim oPath, oPath1
Dim oDate As Date
''
With Sheet2
.Activate
.Cells.Clear
.Cells.Font.Size = 9
''
Str = "Select Distinct Format(日期,'yyyy-mm')" & _
" From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] "
'Debug.Print Str
''
Set Rs = SqlRetuRs(Str)
.Cells(2, 20).CopyFromRecordset Rs
Rs.MoveFirst
For ii = 0 To Rs.RecordCount - 1
If IsDate(Rs.Fields(0)) Then
oDate = Rs.Fields(0)
Str = "Select Format(日期,'yyyy-mm'), Format(日期,'yyyy-mm-dd')" & _
" From [TraverseFolderFile$A1:Z47627] Where 日期 >= #" & Format(oDate, "yyyy/mm/dd") & "# and 日期 < #" & Format(oDate + 1, "yyyy/mm/dd") & "#"
Str = "Select Format(日期,'yyyy-mm'), Format(日期,'yyyy-mm-dd')" & _
" From [TraverseFolderFile$A1:Z47627] Where 日期 >= #" & Format(oDate, "yyyy/mm") & "# and 日期 < #" & Format(oDate + 1, "yyyy/mm") & "#"
Str = "Select distinct Format(日期,'yyyy年mm月'), Format(日期,'yyyy年mm月dd日')" & _
" From [TraverseFolderFile$A1:Z47627] Where 日期 >= #" & Format(oDate, "yyyy/mm") & "# and 日期 < #" & Format(oDate + 32, "yyyy/mm") & "#"
'Str = "Select distinct Format(日期,'yyyy年mm月'), Format(日期,'yyyy年mm月dd日')" & _
" From [TraverseFolderFile$A1:Z47627] Where int(日期)= #" & Format(oDate, "yyyy/mm") & "#"
Set Rs1 = SqlRetuRs(Str)
If Rs1.RecordCount > 0 Then
MergeDate Rs1, Sheet2
End If
End If
Rs.MoveNext
Next ii
End With
Application.DisplayAlerts = True
End Sub

复制代码

tanglf188 · 发表于 2023-10-7 09:22

#是日期型，你再加个单引号又是字符型了；去掉单引号试试呢
Str = "Select 日期 From [TraverseFolderFile$A1:Z47627] Weree 日期 = #2023/6/1#"

ning84 · 发表于 2023-10-8 10:31

Sub ShtMoveJPGtoFolder()
Dim T: T = Time
Debug.Print T
Dim Fso As FileSystemObject
Set Fso = New FileSystemObject
Dim Str, Str1
Dim Sht As Worksheet
Dim Rng As Range
Dim Rs As Recordset
Set Sht = Sheet2
Set Rng = Sht.Cells(900000, 1)
Str1 = " From [" & Sht.Name & "$A1:Z" & Rng.End(xlUp).Row & "] Where 日期 <> Null"
Str = "Select Format(日期,'yyyy年mm月'), Format(日期,'yyyy年mm月dd日')" & _
",文件夹,文件 " & Str1
Str = "Select 文件夹, " & _
"'\' + Format(日期'yyyy年mm月')+ '\' + Format(日期,'yyyy年mm月dd日') + '\'+文件 " & Str1
Str = "Select 文件夹, " & _
"\ + Format(日期'yyyy年mm月')+ \ + Format(日期,'yyyy年mm月dd日') + \+文件 " & Str1
Str = "Select 文件夹, " & _
" Format(日期'yyyy年mm月') + Format(日期,'yyyy年mm月dd日') + 文件 " & Str1
Str = "Select 文件夹, " & _
" Format(日期'yyyy年mm月') + Format(日期,'yyyy年mm月dd日') " & Str1
Str = "Select '\' + Format(日期,'yyyy年mm月')+ '\' + Format(日期,'yyyy年mm月dd日')+ '\' + 文件" & _
",文件夹 " & Str1
Set Rs = SqlRetuRs(Str)
Debug.Print Rs.RecordCount
With Rs
.MoveFirst
For ii = 0 To .RecordCount - 1
Str = .Fields(1)
Str1 = ThisWorkbook.Path & .Fields(0)
With Fso
If .FileExists(Str) = True And .FileExists(Str1) = False Then
Debug.Print Str, Str1
Debug.Print Format(Time - T, "h:mm:ss")
.MoveFile Str, Str1
End If
End With
.MoveNext
Next ii ''
End With
Debug.Print Format(Time - T, "h:mm:ss")
End Sub

复制代码

Select '\' + Format(日期,'yyyy年mm月')+ '\' + Format(日期,'yyyy年mm月dd日')+ '\' + 文件,文件夹 From [TraverseFolderFile$A1:Z47617] Where 日期 <> Null

		自动登录	找回密码
密码			免费注册