• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3937
  • Last Modified:

Julian Date Conversion

I am using SQL server 2000 with data from as/400 dts transfer. I have a date field with julian dates that look like this:

101087

Any idea how to convert this date to regular date in display in frontpage created databse wizard results. I have tried to tweek SQL drive in odbc admin. no luck as of yet.
0
timokeeffe
Asked:
timokeeffe
  • 3
1 Solution
 
amit_gCommented:
0
 
namasi_navaretnamCommented:
101087 does not look like a julian date.

Running the code from above amig_g's link causes overflow

select dateadd(dd,  (101087-2415021), convert(datetime, '1900-01-01',103))

101087 look like a normal date in the format of (mmddyyyy)

0
 
namasi_navaretnamCommented:
Code I downloaded from the link
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=341&lngWId=5

also causes memory over flow when passing your date 101087

0
 
PaulBarbinCommented:
Agreed, if it were a Julian date it would represent roughly 4437 BC.  If its just a date of October 10, 1987 (is it 19?) then something like this might work, depending on whether single digit days and single digit months are padded (example: January 1987 would like like this: 010187)

declare @dt varchar(6)
set @dt = '101087'

select convert(datetime, substring(@dt,1,2) + '-' + substring(@dt,3,2) + '-19' + substring(@dt,5,2))

If its not padded, then you wouldn't be able to distinguish January 11th from November 1st.

Also, my code assumes 19 as the century, you could try to look at the year and decide which century to use (under 50 = 2000, over 50 = 1900).

Paul
0
 
namasi_navaretnamCommented:
Would you need further help? If not please close this issue. :)
Regards.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now