如何转化日期区间表达

hjs　发表于：2013-07-04 00:28:04 　

表有日期字段：如：2010-01-25,……2012-12-21,2012-12-21,2013-01-20……

需要区间划分：如：2011-11-21至2011-12-20表示为201112，2012-12-21至2013-01-20日期间表达为201301，2013-01-21至2013-02-20日期间表达为201302，……，2013-11-21至2013-12-20日期间表达为201312，2013-12-21至2014-01-20日期间表达为201401，以此类推，如何使用函数或者查询表示！

出现格式错误，见例子！

点击下载此附件

Top

煮江品茶发表于：2013-07-04 10:44:20

写一个自定义函数，用DateAdd计算期间，并返回值即可。

Top

hjs 发表于：2013-07-04 13:20:12

出现错误，请老师看看！

Top

煮江品茶发表于：2013-07-04 14:47:28

鬼打架，你把计算的起点日期往前写不就完事了，比如1900/12/20。

SELECT 表1.rq, GetMyMonth(#1/20/1900#,[rq]) AS a
FROM 表1;

Top

煮江品茶发表于：2013-07-04 15:28:52

Function GetMyMonth(ByVal date0 As Date, ByVal D As Date) As String
    '功能：返回工厂日历计算的月度
    '参数：D --- 计算日期
    '示例：select *,GetMyMonth(#1900/12/20#,[日期]) from tbname
    Dim d0 As Date, d1 As Date
    Dim str As String
    d0 = date0
    d1 = DateAdd("m", 1, d0)
    str = ""
    If d0 <= D Then
        Do While (D >= d0 And D < d1) = False
            d0 = d1
            d1 = DateAdd("m", 1, d0)
        Loop
        str = Format(d1, "yyyymm")
    End If
    GetMyMonth = str
End Function

Top

hjs 发表于：2013-07-04 16:35:38

运行了，但是效率不高，自己重新整了个！

Top

hjs 发表于：2013-07-04 16:36:58

Function GetMyMonth1(ByVal D As Date) As String
    '功能：返回工厂日历计算的月度
    '参数：D --- 计算日期
    '示例：select *,GetMyMonth([日期]) from tbname
    Dim d0 As String, d1 As String
    Dim str As String
    str = ""
    d0 = Format(D, "mmdd")
    d1 = Format(D, "yyyy")
    Select Case d0
    Case "0101" To "0120"
    str = d1 & "01"
    Case "0121" To "0220"
    str = d1 & "02"
    Case "0221" To "0320"
    str = d1 & "03"
    Case "0321" To "0420"
    str = d1 & "04"
    Case "0421" To "0520"
    str = d1 & "05"
    Case "0521" To "0620"
    str = d1 & "06"
    Case "0621" To "0720"
    str = d1 & "07"
    Case "0721" To "0820"
    str = d1 & "08"
    Case "0821" To "0920"
    str = d1 & "09"
    Case "0921" To "1020"
    str = d1 & "10"
    Case "1021" To "1120"
    str = d1 & "11"
    Case "1121" To "1220"
    str = d1 & "12"
    Case "1221" To "1231"
    str = Format(D, "yyyy") + 1
    str = str & "01"
    Case Else
    str = "-"
    End Select
    GetMyMonth1 = str
End Function

Top

网行者发表于：2013-07-04 21:40:40

也看看这个:

Function MonthofCompany(Byval Mdate As Date) As String
'功能：返回工厂日历计算的月度
    '参数：Mdate --- 计算日期
    '示例：select *,MonthofCompany([日期]) from tblname
    Dim D As Long, M As Long, Y As Long, Myyear As Long, Mymonth As Long
    D = Day(Mdate)
    M = Month(Mdate)
    Y = Year(Mdate)
    Myyear = IIf(M = 12 And D >= 21, Y + 1, Y)
    Mymonth = IIf(IIf(D >= 21, M + 1, M) > 12, 1, IIf(D >= 21, M + 1, M))
    MonthofCompany = Myyear & Format(Mymonth, "00")
End Function

Top

总记录：7篇页次：1/1 9 1 :

Access交流中心

[5分]如何转化日期区间表达