Julian date to regular date conversion

Question

Post reply

Julian date to regular date conversion

QQ-485619

Ten Centuries

Points: 1191
More actions
November 5, 2010 at 2:48 pm

#388176

I have a column called paiddate in a table called Checks, and the value in this column is in julian date
How can I convert it into a regular date?
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

it depends on the format of the julian date; here's a couple of examples, but post back how your data is formatted;

DECLARE @sdate int

SET @sdate = 109252

--in AS400/DB2 date is 01/01/1900 + 109 years + 252 days

select dateadd(day,@sdate % 1000,dateadd(year,(@sdate /1000) -1,convert(datetime,'01/01/1900')))

or

select dateadd(dd, (@jdate - ((@jdate/1000) * 1000)) - 1, dateadd(yy, @jdate/1000, 0))

SELECT dbo.fn_JulianDateConversion('1997090101636')

ALTER FUNCTION [dbo].[fn_JulianDateConversion](@JulianDate VARCHAR(13))

RETURNS DATETIME

AS

BEGIN

--comments used for testing

--declare @JulianDate varchar(30)

--SET @JulianDate = '1997090101636'

DECLARE @ConvertedDate DATETIME,

@JulianDateYear INT,

@JulianDateDays INT,

@JulianHour INT,

@JulianMinute INT,

@JulianSecond INT,

@ConcatInfo DATETIME --mm/dd/yyyy HH:MM:SS

SELECT

@JulianDateYear = CONVERT(INT,SUBSTRING(@JulianDate,1,4)),

@JulianDateDays = CONVERT(INT,SUBSTRING(@JulianDate,5,3)),

@JulianHour = CONVERT(INT,SUBSTRING(@JulianDate,8,2)),

@JulianMinute = CONVERT(INT,SUBSTRING(@JulianDate,10,2)),

@JulianSecond = CONVERT(INT,SUBSTRING(@JulianDate,12,2))

SET @JulianDateYear = @JulianDateYear - 1900

SELECT @ConcatInfo =

dateadd(second,@JulianSecond, --seconds

dateadd(minute,@JulianMinute, --minutes

dateadd(hour,@JulianHour, --hours

dateadd(day,@JulianDateDays, --days

dateadd(year,@JulianDateYear,0))))) --year

return @ConcatInfo

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

It depends on what you really mean by Julian date. There are a couple of common meanings that have nothing to do with each other (refer to link below).

More than likely, you mean some variation of an ordinal date, so the best thing would be for you to post 5 combinations of paiddate and the calendar date that it matches. Then someone should be able to help you.

http://en.wikipedia.org/wiki/Julian_Day

"The Julian day or Julian day number (JDN) is the number of days that have elapsed since 12 noon Greenwich Mean Time (UT or TT) on Monday, January 1, 4713 BC in the proleptic Julian calendar 1. That day is counted as Julian day zero. The Julian day system was intended to provide astronomers with a single system of dates that could be used when working with different calendars and to unify different historical chronologies.

...

The term Julian date is also used to refer to:

Julian calendar dates

ordinal dates (day-of-year)

The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect."

Philipp Post Default port Points: 1401 More actions · Answer 3

You could also use a calendar table and fill it with the date ranges needed and join it in your queries.

brgds

Philipp Post