SQL Julian date to convensional date

Good day folks,

I'm currently in the process of coverting some data from the our AS400 onto MSSQL DB ( SQL2000). The problem I'm having is converting Julian dates on MSSQL. Can anybody help be with a script that can be run on MSSQL that will convert the julian date to conventional date ( MM/DD/YYYY)?

Your help with in this regard will truly be appreciated.

JL

Here's some code I wrote a number of years ago to convert JDE dates to
Microsoft dates inside Microsoft Access. You should be able to do
something similar for SQL Server. You can also do some more searches on
the forum. Over the years, several people have posted different methods
for doing date conversions



Public Function CvtJDEdate2MSdate(ByVal lngJDEdate As Long) As Date



Dim intLen As Integer

Dim strDate As String

Dim intYear As Integer

Dim intDayInYear As Integer



strDate =3D Format(lngJDEdate, "000000")

intLen =3D Len(strDate)

intYear =3D 1900 + CInt(Left(strDate, intLen - 3))

intDayInYear =3D Right(strDate, 3)



CvtJDEdate2MSdate =3D DateAdd("d", intDayInYear - 1, ("1/1/" &
intYear))



End Function





Ellen Deak

Senior Systems Developer

Cooper Standard Automotive

JDE EnterpriseOne (OneWorld XE Update 7 SP23_M1), AS400 DB2 V5R3M0,
Citrix Clients

Hi JL,

Hope the following script will solve your problem.

declare @DateP char(10)
select @datep=101150
dateadd(dd,@datep-(@datep/1000)*1000-1, convert(datetime,”01/01/”+ right(convert(varchar,@datep/1000),2))) as Date_ENT


Regards
Kumar

8.11 SP1 8.96 C1, Oracle 10g, Win2K

CNC Pro <[email protected]> wrote:
Good day folks,

I'm currently in the process of coverting some data from the our AS400 onto MSSQL DB ( SQL2000). The problem I'm having is converting Julian dates on MSSQL. Can anybody help be with a script that can be run on MSSQL that will convert the julian date to conventional date ( MM/DD/YYYY)?

Your help with in this regard will truly be appreciated.

JL JL OneWorld Xe Update 7 SP23_K1 ES= AS/400 ( V5R3 ) Deply = WIN2K Client WIN2K Citrix Presentation3

Re: SQL Julian date to conventional date

If there are no blank dates, use this:

CAST('1/1/' + CAST(1900 + FLOOR(GLDGJ / 1000) AS CHAR) AS DATETIME) + CAST(GLDGJ AS INT) % 1000 - 1 AS [General Ledger Date]

If there are blank dates, use this:

CASE WHEN YADT != 0 THEN CAST('1/1/' + CAST(1900 + FLOOR(YADT / 1000) AS CHAR) AS DATETIME) + CAST(YADT AS INT) % 1000 - 1 ELSE NULL END AS [Termination Date]