Julian to Gregorian and Gregorian to Julian

Are there SQL functions to convert back and forth between these two?
TIA for the help.
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenetmyob@.beatles.com wrote:

> Are there SQL functions to convert back and forth between these two?
> TIA for the help.
> -- Posted with NewsLeecher v3.0 Beta 6
> -- http://www.newsleecher.com/?usenet
Not in SQL Server. This is a hard problem because no single,
universally valid conversion between Julian and Gregorian is possible.
The answer depends on what time, place and proleptic calendar you take
as your reference point. I suggest you create your own Calendar table
to do the conversion.
The last word on all the messy details can be found here:
http://www.tondering.dk/claus/calendar.html
David Portas
SQL Server MVP
--|||David Portas wrote:
> Not in SQL Server. This is a hard problem because no single,
> universally valid conversion between Julian and Gregorian is possible.
Clarification: What I mean is that any conversion you choose to apply
may or may not be factually, historically or legally accurate dependent
on geography and the date in question. You can of course choose to use
a single conversion but its validity may be only theoretical.
David Portas
SQL Server MVP
--

Julian Day

I have created an auxiliary calendar table with the help of
http://www.aspfaq.com/2519
I would like to add a column for the daynumber of year (e.g. 1-365), and
populate it. I am new to sql and am not finding help with this. Any help
would be greatly appreciated.select datepart(dayofyear, getdate())
-- or
select datepart(dy, getdate())
"Nice_Out" wrote:

> I have created an auxiliary calendar table with the help of
> http://www.aspfaq.com/2519
> I would like to add a column for the daynumber of year (e.g. 1-365), and
> populate it. I am new to sql and am not finding help with this. Any help
> would be greatly appreciated.|||Thanks.
"KH" wrote:
> select datepart(dayofyear, getdate())
> -- or
> select datepart(dy, getdate())
>
> "Nice_Out" wrote:
>sql

Julian Dates

Is there a function or quick way to convert a calendar date to a julian
date. For example 1 September 2004 to 4245.
Rus BaileyIs Julian date days from a particular date? If so, you could just use
dateadd()/datediff().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rus Bailey" <Rus.Bailey@.dla.mil> wrote in message
news:OSn9uCFkEHA.3476@.tk2msftngp13.phx.gbl...
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>|||I used a Date Time Picker and a label and it worked for me here is the code I
used
lblJulian.Text = DateTimePicker1.Value.DayOfYear
Hope this helps
"Rus Bailey" wrote:
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>
>

Julian Dates

I want to derive a date from a string field where the string will have the date embeded in the following format YYJJJ, for example 02364 = Dec 30 2002. Is there a way to do this using Transact-Sql via Sql Server?-- If it where in a procedure
DECLARE @.strDate varchar(5)
DECLARE @.dtDate smalldatetime

SET @.strDate = '02364'

SET @.dtDate = DATEADD(dd, CAST(RIGHT(@.strDate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(@.strDate, 2), 3))

SELECT @.dtDate

-- Using within a query
CREATE TABLE #tmp_mydates (mydate varchar(5))

INSERT INTO #tmp_mydates VALUES ('02364')
INSERT INTO #tmp_mydates VALUES ('02264')
INSERT INTO #tmp_mydates VALUES ('02164')
INSERT INTO #tmp_mydates VALUES ('02004')
INSERT INTO #tmp_mydates VALUES ('02002')

SELECT mydate, DATEADD(dd, CAST(RIGHT(mydate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(mydate, 2), 3)) AS myconverteddate FROM #tmp_mydates|||I want to derive a date from a string field where the string will have the date embeded in the following format YYJJJ, for example 02364 = Dec 30 2002. Is there a way to do this using Transact-Sql via Sql Server?

Q1 [Is there a way to do this using Transact-Sql via Sql Server?]
A1 You may consider creating your own derived "JulianDate" user datatype; and also creating your own user functions or stored procedures to properly interpet, convert to and from, and generally handle them as you might require. (I've found the strategy helpful in handling similar customized Julian date formats for special applications) For example:
dbo.fn_MyJulianDate_Add,
dbo.fn_MyJulianDate_Diff,
dbo.fn_ConvertStandardDate_To_MyJulianDate, dbo.fn_ConvertMyJulianDate_To_StandardDate,
etc.

Julian Datehelp :(

I have a julian date(04159) and I need to convert this to any normal date format yyyymmdd or mmddyy or basically anything that I can work with! I am just about fed up with trying to convert this. It would be simple enough if there want leap years. Does anyone have a function or something out there that can convert this for me? Code that I found on the web turned out to be bogus. Your help will be greatly appreciated. If you have something in VB that would work also. SQLServer preferably.

ThanksIt may be off by a day or so, but it's a start:

select dateadd(day, cast(reverse(cast(reverse('04159') as char(3))) as int), '01/01/' + cast('04159' as char(2)))|||Yeah...Thats sorta where I left off in my efforts. Thanks for your input. I will eventually get it...Hopefully someone in here will see this and have something.|||This is ancient old code, from when SQL 2000 was still in beta, but I think it works. Give this a try:CREATE FUNCTION dbo.f_jDate(@.piJdate INT) RETURNS DATETIME AS
BEGIN
RETURN DateAdd(day, (@.piJdate % 1000) - 1, Convert(DATETIME
, CASE WHEN @.piJdate < 50000 THEN '20' ELSE '19' END
+ Replace(Str(@.piJdate / 1000, 2), ' ', '0') + '-01-01'))
END-PatP|||Thanks Pat....That worked perfectly! Your my hero for the day!

Julian date time Conversion

Can anyone tell me how to convert julian date time to DateTime and Vice Versa?
the function which I have only convers the date to Julian and julian to date but the time is not appended.
How can i get the time into Julian format and from julian format?
Any help would be appreciated.
thanks.If you poke around the source forthis page, you'll find the JavaScript that they use to do it.
|||

Try the links below for UDF Julian to DateTime conversion code. Hope this helps.
http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-3-udf_DT_FromJulian.htm

http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-2-udf_DT_ToJulian.htm

JULIAN DATE

No Doubt Julian Date conversion is a PAIN in RS. After reading many postings
I came up with the following on the fly conversion for a julian date in our
AS/400 system:
In order to convert the following date 107306 to the regular date: 11/3/2007
I used the following:
First of, the first 2 digits denote the Century: 00 stands for 1900 and 10
stands for 2000
So in the case of 107306 the first 3 digits stand for 2007 and the last 3
stand for number of days.
Here's the formula:
=DATEADD("d", Val(Right(Fields!TransactionDate.Value,3)), "#01/01/200" & Cstr
(Mid(Fields!TransactionDate.Value,3,1)) & "#")
I just wish that when Microsoft marketed RS they had their act together as
far as usability is cocerned, you almost have to be a rocket scientist to
make the reports half way decent! and DON'T get me started on the
ridiculously limited charting functionality!!! Being a recovering Crystal-
holic it has been an absolute BEAR to mimic functionality and looks...
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200712/1On Dec 12, 12:14 pm, "patrickpk via SQLMonster.com" <u38565@.uwe>
wrote:
> No Doubt Julian Date conversion is a PAIN in RS. After reading many postings
> I came up with the following on the fly conversion for a julian date in our
> AS/400 system:
> In order to convert the following date 107306 to the regular date: 11/3/2007
> I used the following:
> First of, the first 2 digits denote the Century: 00 stands for 1900 and 10
> stands for 2000
> So in the case of 107306 the first 3 digits stand for 2007 and the last 3
> stand for number of days.
> Here's the formula:
> =DATEADD("d", Val(Right(Fields!TransactionDate.Value,3)), "#01/01/200" & Cstr
> (Mid(Fields!TransactionDate.Value,3,1)) & "#")
> I just wish that when Microsoft marketed RS they had their act together as
> far as usability is cocerned, you almost have to be a rocket scientist to
> make the reports half way decent! and DON'T get me started on the
> ridiculously limited charting functionality!!! Being a recovering Crystal-
> holic it has been an absolute BEAR to mimic functionality and looks...
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200712/1
I don't know what your date format is, but it isn't a Julian Date --
it's some kind of weird mainframe hybrid of Century, Year and
DayOfYear. A Julian Date is a count of days since Jan 1st, 4713 BC.
(See http://aa.usno.navy.mil/data/docs/JulianDate.php). The Julian
date for 11/3/2007 is 2,454,408.
For you, the following .Net formula works to convert to 11/3/2007, and
it gets your 1900-2000 span right too (replace the number with your
Fields...Value):
= #1/1/1900#.AddYears( Floor( 107306 / 1000) ).AddDays( 107306 Mod
1000 )
If you needed to go backwards for some reason, this works (replace the
date with your Fields...Value) :
= 1000 * ( CDate(#11/3/2007#).Year - 1900 ) +
CDate(#11/3/2007#).DayOfYear - 1
-- Scottsql

Julian Date

A table that I am querying from has the date field in julian date format "04194". I am only wanting to pull the information from this table that is => than todays date. Maybe some of you have done this before and can give a little help in finding the best way to do this?You could take today's date and subtract it from the first day of the year (DATEDIFF ( datepart , startdate , enddate ) ) to give you an int value for the day of the year (actually day of year -1 since 20040101 - 20040101 = 0). Then convert to a 3 character value and concatinate the two digit year to the front and ... tadaaa ... the julian date.|||? I am not following you?|||You can get the Julian date using:SELECT 1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate())If you need it in character format, you can use:SELECT Replace(Str(
1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate()), 5), ' ', '0')After you've got that, the rest should just be a simple compare.

-PatP|||Or you could go the other way so you can utilize all of SQL Server's functions

DECLARE @.julian char(5), @.gregorian datetime
SELECT @.julian = '04194'
SELECT @.gregorian = DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3)),CON VERT(datetime,'20'+SUBSTRING(@.julian,1,2)+'/01/01'))
SELECT DATEDIFF(dd,GetDate(),@.gregorian)|||Perfect!...Thanks for your help. I was going about it the wrong way. I was trying to convert the julian date and then compare.|||Thanks also Brett That was a road I was eventually going to have to cross.|||udf's...Makes a perfect house warming gift

CREATE FUNCTION udf_JulianToGregorian(@.julian char(5))
RETURNS datetime
AS
BEGIN
DECLARE @.gregorian datetime
SELECT @.gregorian =
DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3))
,CONVERT(datetime
,CASE WHEN SUBSTRING(@.julian,1,2) BETWEEN '00' AND '50'
THEN '20'
ELSE '19'
END
+SUBSTRING(@.julian,1,2)+'/01/01'))
RETURN @.gregorian
END
GO

DECLARE @.julian char(5)
SELECT @.julian = '04194'
SELECT dbo.udf_JulianToGregorian(@.julian)
GO