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!