populate a calendar table with julian and calendar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

populate a calendar table with julian and calendar

Author

Topic

SGARCIA09
Starting Member

3 Posts

Posted - 2009-07-31 : 11:00:34

I need to pupulate a table one colummn Julian and another with calendar date from 2010 to 2020.

The function from Julian date is:

CREATE function [dbo].[NtoJDEDATE](@NDATE datetime)
returns int
as
begin
return str(datepart(yyyy, @NDATE)-1900,3) + replace(str(datepart(y, @NDATE),3 ),' ','0')
end

Thank in advance for the help

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-31 : 13:23:53

Care to pose a question?

EDIT:

And is this really Julian?


DECLARE @NDATE datetime
SET @NDATE = '1/1/2009'
SELECT str(datepart(yyyy, @NDATE)-1900,3) + replace(str(datepart(y, @NDATE),3 ),' ','0') AS Julian

Julian
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
109001

(1 row(s) affected)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-31 : 14:13:19

[code]
create table #t (gregorian datetime, julian int)

Declare @dt smalldatetime
SET @dt = '20100101'
WHILE @dt < '20210101'
BEGIN
INSERT #t (gregorian, julian)
VALUES (@dt, Convert(int, str(datepart(yyyy, @dt)-1900,3) + replace(str(datepart(y, @dt),3 ),' ','0')))

SET @dt = @dt + 1
END

SELECT * FROM #t
DROP TABLE #t[/code]

SGARCIA09
Starting Member

3 Posts

Posted - 2009-07-31 : 14:16:39

Hi Brett,

Yes it is Julian Date...

SGARCIA09
Starting Member

3 Posts

Posted - 2009-07-31 : 14:19:51

Hi russell

Thank you very much for you answer. This is all that I need

Sonia

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-31 : 15:25:28

so the leading 1 is for the millennium?

EDIT: What happens when we hit a 2 digit millennium? It's the y10m bug! Starships start falling out of space?

EDIT: I thought Julian was YYDDD

EDIT: Whats this

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

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-31 : 15:43:22

It's not a Julian date, it just a strange variation of an Ordinal Date where the calendar year YYYY is replaced with a year offset from 1900:
http://en.wikipedia.org/wiki/Ordinal_date
"An ordinal date is a calendar date consisting of a year and a day of year ranging between 1 and 366 (starting on January 1). The two numbers can be formatted as YYYY-DDD to comply with the ISO 8601 ordinal date format."

http://en.wikipedia.org/wiki/Julian_day
"The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect, however it is widely used that way in the earth sciences and computer programming."

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources