Cheney Shue

导航

统计
 

JDE使用的是Julian日期,其格式是6位的整数。第一位是世纪,1代表21世纪,0代表20世纪;第二和第三位是年,比如1997年是97;剩下三位是一年中的第几天,比如2007年的第123天的6位日期数是107123。

下面的代码可以编译后可用在任何环境。

例如,在SQL Server数据库引擎中建立JDE日期转换的函数。

1. 建立一个新SQL Server项目





2. 新建数据库引用




3. 在项目中新建用户定义函数




4. 加入下面代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class JdeTime
    '日期增减
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function JdeAddDay(ByVal JdeDate As Integer, ByVal TimeType As String, ByVal Interval As Integer) As Integer
        Dim dt As DateTime = Jde2Date(JdeDate)
        Select Case TimeType
            Case "y", "Y" '增加年
                Return Date2Jde(dt.AddYears(Interval))
            Case "M", "m" '增加月
                Return Date2Jde(dt.AddMonths(Interval))
            Case "D", "d" '增加日
                Return Date2Jde(dt.AddDays(Interval))
            Case Else
                Return Nothing
        End Select
    End Function

    '两日期差值
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function JdeDayDiff(ByVal JdeDate1 As Integer, ByVal JdeDate2 As Integer, ByVal TimeType As String) As Integer
        Dim dt1 As DateTime = Jde2Date(JdeDate1)
        Dim dt2 As DateTime = Jde2Date(JdeDate2)
        Select Case TimeType
            Case "y", "Y" '两日期相差的年数
                Return CInt(DateDiff(DateInterval.Year, dt1, dt2))
            Case "M", "m" '两日期相差的月数
                Return CInt(DateDiff(DateInterval.Month, dt1, dt2))
            Case "D", "d" '两日期相差的天数
                Return CInt(DateDiff(DateInterval.Day, dt1, dt2))
            Case "X", "x" '两日期相差的旬数
                Return CInt(DateDiff(DateInterval.Month, dt1, dt2)) * 3 + JdeDayPart(JdeDate1, "x") - JdeDayPart(JdeDate2, "x")
            Case Else
                Return Nothing
        End Select
    End Function

    '第一天
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function JdeFirstDay(ByVal JdeDate As Integer, ByVal TimeType As String) As Integer
        Dim y As Integer = JdeDayPart(JdeDate, "y")
        Dim m As Integer = JdeDayPart(JdeDate, "m")
        Dim x As Integer = JdeDayPart(JdeDate, "x")
        Select Case TimeType
            Case "y", "Y" '当年的第一天
                Return Date2Jde(CDate(y.ToString + "-01-01"))
            Case "M", "m" '当月的第一天
                Return Date2Jde(CDate(y.ToString + "-" + m.ToString + "-01"))
            Case "X", "x" '当旬的第一天
                Return Date2Jde(CDate(y.ToString + "-" + m.ToString + "-" + ((x - 1) * 10 + 1).ToString))
            Case Else
                Return Nothing
        End Select
    End Function

    '最后一天
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function JdeLastDay(ByVal JdeDate As Integer, ByVal TimeType As String) As Integer
        Dim y As Integer = JdeDayPart(JdeDate, "y")
        Dim m As Integer = JdeDayPart(JdeDate, "m")
        Dim x As Integer = JdeDayPart(JdeDate, "x")
        Select Case TimeType
            Case "y", "Y" '当年的最后一天
                Return Date2Jde(CDate((y + 1).ToString + "-01-01").AddDays(-1))
            Case "M", "m" '当月的最后一天
                Return JdeAddDay(JdeAddDay(JdeDate, "m", 1), "d", -1)
            Case "X", "x" '当旬的最后一天
                If x = 1 Then
                    Return Date2Jde(CDate(y.ToString + "-" + m.ToString + "-10"))
                ElseIf x = 2 Then
                    Return Date2Jde(CDate(y.ToString + "-" + m.ToString + "-20"))
                Else
                    Return JdeLastDay(JdeDate, "m")
                End If
            Case Else
                Return Nothing
        End Select
    End Function

    '日期的部分
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function JdeDayPart(ByVal JdeDate As Integer, ByVal TimeType As String) As Integer
        Dim dt As DateTime = Jde2Date(JdeDate)
        Select Case TimeType
            Case "y", "Y" '年
                Return dt.Year
            Case "M", "m" '月
                Return dt.Month
            Case "D", "d" '日
                Return dt.Day
            Case "x", "X" '旬
                If dt.Day > 10 AndAlso dt.Day <= 20 Then
                    Return 2
                ElseIf dt.Day > 20 Then
                    Return 3
                Else : Return 1
                End If
            Case "DW", "dw", "dW", "Dw" '星期,星期天是0
                Return dt.DayOfWeek
            Case "DY", "dY", "Dy", "dy" '一年中的第几天
                Return dt.DayOfYear
            Case Else
                Return Nothing
        End Select
    End Function

    '当前日期
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function JdeDay() As Integer
        Return Date2Jde(Date.Now)
    End Function

    'DateTime格式转换为JDE格式
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function Date2Jde(ByVal DateTime As DateTime) As Integer
        Return (DateTime.Year - 1900) * 1000 + DateTime.DayOfYear
    End Function

    'JDE格式转换为DateTime格式
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function Jde2Date(ByVal JdeDate As Integer) As DateTime
        Dim v As DateTime = #1/1/1900#
        v.AddYears(CInt(JdeDate / 1000))
        v.AddDays((JdeDate Mod 1000) - 1)
        Return v
    End Function
End Class



5. 部署项目




6. OK!

posted on 2007-06-29 17:58 Cheney Shue 阅读(...) 评论(...) 编辑 收藏