converting julian day to gregorian date

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

converting julian day to gregorian date

Author

Topic

CBee
Starting Member

15 Posts

Posted - 2010-11-20 : 22:26:58

As you all know, julian day is the number of the day since some time in ancient history and gregorian date is in the format mmddyyyy or yyyymmdd or a similar format.
I have a table containing a column of julian days that I need to convert to gregorian dates. I know how to cast a single julian day to gregorian but need to convert the complete column. There is no index key in the table.
I have attempted to export the data and then bulk insert the data into another table for which I have set the column data type to smalldatetime but get the following error for each line in the text file:
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (dater).

Any help will be greatly appreciated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-20 : 23:00:30

let's see your conversion code

CBee
Starting Member

15 Posts

Posted - 2010-11-20 : 23:21:20

Is this what you are asking for?
select cast (40259 as datetime)

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-21 : 00:23:32

No, I don't know that

Julian is yyyyddd

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

CBee
Starting Member

15 Posts

Posted - 2010-11-21 : 08:31:13

Julian days simply enumerate the days and fraction which have elapsed since the start of the Julian era, which is defined as beginning at noon on Monday, 1st January of year 4713 B.C.E. in the Julian calendar.
What you are referring to by yyyyddd is the Julian DATE, not Julian DAY.

CBee
Starting Member

15 Posts

Posted - 2010-11-21 : 09:02:51

But back to my question:
The original data set is:
table: juliandays
column: date
40102
40103
40104
40105
40106
40107
40108
40109
40110
40111

desired result:
table: smalldate
column: dater (smalldatetime but could be datetime)
2009-10-18 00:00:00.000
2009-10-19 00:00:00.000
2009-10-20 00:00:00.000
2009-10-21 00:00:00.000
2009-10-22 00:00:00.000
2009-10-23 00:00:00.000
2009-10-24 00:00:00.000
2009-10-25 00:00:00.000
2009-10-26 00:00:00.000
2009-10-27 00:00:00.000

result determined by the following query:
select cast (40102 as datetime)

juliandays data set contains over 4k rows.

If it were possible to merely convert column juliandays:date from varchar to smalldatetime or datetime that would be great.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-21 : 10:44:25

You already know how to do this, so not sure what the questions is.

UPDATE yourTable SET dater = CAST (julDay as smalldatetime);

4k rows is very small. update will execute extremely fast. unless you have a bunch of triggers or something

CBee
Starting Member

15 Posts

Posted - 2010-11-21 : 21:15:25

the question is exactly how do I use the query you provided.
I issue UPDATE juliandays SET dater = CAST (date as smalldatetime);
I get the error invalid column name "dater".
If I issue UPDATE juliandays SET date = CAST (date as smalldatetime);
I get the error "Conversion failed when converting character string to smalldatetime data type."
Still confused.
Charlie

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-21 : 21:46:47

You can just add the day-1 from the julian date and add it to the 1/1/yearofjuliandate if I am not mistaken.

Update juliandays
set datetimecolumn = DATEADD(DAY, substring(cast(date as char(7)),5,3) - 1, '01/01/' + substring(cast(date as char(7)),1,4))

Poor planning on your part does not constitute an emergency on my part.

CBee
Starting Member

15 Posts

Posted - 2010-11-21 : 22:15:36

I'm not using julian DATE, my data is julianDAY.
The query suggested by dg returns a result with the year 4010 from my data

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-21 : 22:43:17

Seems like those aren't Julian days, but rather simply the days since the 0 based date in SQL, so casting that integer as datetime should produce the desired results as previously stated by others. Sorry for the confusion, because I can get it to work with the sample data provided.

As to your errors, the invalid column name is self-explanatory, so double check that

Do you have any non-integer data in that column that would cause a conversion issue?

You can always use Case When isDate(JulianDaycolumn) = 1 then cast(JulianDayColumn as datetime) else null end

Test using SELECT on the column for conversion.

Poor planning on your part does not constitute an emergency on my part.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-21 : 22:51:43

show us your table definition please

CBee
Starting Member

15 Posts

Posted - 2010-11-21 : 23:29:32

table juliandays
column date (varchar(15),null)

is that the problem? date should be an integer instead of varchar?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-22 : 08:08:35