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!
