May 19, 2011 at 3:29 pm
The boss wants to be able to SUM dates. In the mainframe world, this would have been possible with Julian-style dates. The Julian format consists of the year (YY or YYYY) and the day of the year (January 1 would be 001, for example). So January 1, 2011 would be either 11001 or 2011001. Does SQL Server have a data type that does this, or something similar?
May 19, 2011 at 4:20 pm
All that is required is to create a Julian Date Function:
http://www.sqlservercentral.com/Forums/Topic438807-8-1.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 10:24 pm
This will convert a datetime value to the ordinal date (what you calling Julian date).
select
OrdinalDate =
(1000*datepart(year,a.[DATE]))+
datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 ,
a.*
from
( -- Test Data
select [DATE] = getdate()union all
select [DATE] = getdate()+37
) a
order by
a.[DATE]
Results:
OrdinalDate DATE
----------- -----------------------
2011140 2011-05-20 00:19:05.800
2011177 2011-06-26 00:19:05.800
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."
May 19, 2011 at 11:07 pm
imani_technology (5/19/2011)
The boss wants to be able to SUM dates.
What exactly does that mean because it doesn't take too many dates to overwhelm an integer datatype?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2011 at 1:34 am
Michael Valentine Jones (5/19/2011)
"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
I'm no scientist, but that just seems bizarre (or is the wiki article wrong).
According to that definition, 11:59 am on January 2 will still be day 0!!
May 20, 2011 at 10:15 am
Ian Scarlett (5/20/2011)
Michael Valentine Jones (5/19/2011)
"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 BCI'm no scientist, but that just seems bizarre (or is the wiki article wrong).
According to that definition, 11:59 am on January 2 will still be day 0!!
According to the converter on the The United States Naval Observatory (USNO) web site, that is correct.
Calendar Date to Julian Date Converter
http://www.usno.navy.mil/USNO/astronomical-applications/data-services/cal-to-jd-conv
The Julian date for BCE 4713 January 2 11:59:59.0 UT is JD 0.999988
The Julian date for BCE 4713 January 2 12:00:00.0 UT is JD 1.000000
The Julian date for CE 2011 May 20 00:00:00.0 UT is JD 2455701.500000
The Julian date for CE 2011 May 20 11:59:59.9 UT is JD 2455701.999999
The Julian date for CE 2011 May 20 12:00:00.0 UT is JD 2455702.000000
May 20, 2011 at 10:42 am
I'm sure that the USNO has it's reasons but the US Military Finance and Accounting, etc advances the Julian Date at midnight as opposed to noon.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2011 at 12:23 pm
Welsh Corgi (5/20/2011)
I'm sure that the USNO has it's reasons but the US Military Finance and Accounting, etc advances the Julian Date at midnight as opposed to noon.
That is something else, probably the ordinal date. I doubt that US Military Finance and Accounting would be using a date system that is designed for astronomical observations.
The definition of Julian Date is well defined as described in the article, and the USNO is just following that.
May 20, 2011 at 12:54 pm
Michael Valentine Jones (5/20/2011)
Welsh Corgi (5/20/2011)
I'm sure that the USNO has it's reasons but the US Military Finance and Accounting, etc advances the Julian Date at midnight as opposed to noon.That is something else, probably the ordinal date. I doubt that US Military Finance and Accounting would be using a date system that is designed for astronomical observations.
The definition of Julian Date is well defined as described in the article, and the USNO is just following that.
Respectfully, it is not just Finance & Accounting and the Military.
They issue Desk Calenders to military and DOD Civilians that have the Julian Date on the top right hand corner.
1 JAN is Julian Date 1
31 JAN is Julian Date 31
1 Feb is Julian Date 32
31 DEC is Julian Date 365 (unless it is a leap year)
Perhaps they should throw out all of those calenders that they are using.
In the private sector they have Calendar Date to Julian Date Conversion Mappings.
If you use JD Edwards for instance and you want the corresponding calendar date, it does not translate to 12:00 Noon.
Same with SAP.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2011 at 1:07 pm
Thanks for clarifying that Michael.
The trouble is, there are so many definitions of Julian date.
The OP mentions mainframe dates... His definition of a Julian date is in the form YYDDD. On my first mainframe, in ICL COBOL, day 1 was 1st January 1900 (which we used to call Julian dates as well), yet the Wiki article mentions COBOL dates as starting from 1st January 1601 :unsure:
It's bad enough with half the world writing dates as DD/MM/YYYY and the other half using MM/DD/YYYY, without getting into Julian dates.
PS. Welsh Corgi... are you happier now you're called a "Babe" rather than a "Kid" 😀
May 20, 2011 at 1:34 pm
Welsh Corgi (5/20/2011)
Michael Valentine Jones (5/20/2011)
Welsh Corgi (5/20/2011)
I'm sure that the USNO has it's reasons but the US Military Finance and Accounting, etc advances the Julian Date at midnight as opposed to noon.That is something else, probably the ordinal date. I doubt that US Military Finance and Accounting would be using a date system that is designed for astronomical observations.
The definition of Julian Date is well defined as described in the article, and the USNO is just following that.
Respectfully, it is not just Finance & Accounting and the Military.
They issue Desk Calenders to military and DOD Civilians that have the Julian Date on the top right hand corner.
1 JAN is Julian Date 1
31 JAN is Julian Date 31
1 Feb is Julian Date 32
31 DEC is Julian Date 365 (unless it is a leap year)
Perhaps they should throw out all of those calenders that they are using.
In the private sector they have Calendar Date to Julian Date Conversion Mappings.
If you use JD Edwards for instance and you want the corresponding calendar date, it does not translate to 12:00 Noon.
Same with SAP.
That is the ordinal date. As the article I posted a link to says:
"The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect."
That doesn't mean that using the term "Julian date" to refer to the ordinal date it is not in common usage, and that is what the OP, Military, JD Edwards, SAP, and so on are doing.
http://en.wikipedia.org/wiki/Ordinal_date
"An ordinal date is a calendar date typically consisting of a year and a day of year ranging between 1 and 366 (starting on January 1), though year may sometimes be omitted. The two numbers can be formatted as YYYY-DDD to comply with the ISO 8601 ordinal date format."
In addition, Julian date is also used to refer to dates on the Julian calendar, but that is not in widespread use, except in some Orthodox churches.
Plenty of confusion to go around.
May 20, 2011 at 1:50 pm
Michael Valentine Jones (5/20/2011)
Welsh Corgi (5/20/2011)
Michael Valentine Jones (5/20/2011)
Welsh Corgi (5/20/2011)
I'm sure that the USNO has it's reasons but the US Military Finance and Accounting, etc advances the Julian Date at midnight as opposed to noon.That is something else, probably the ordinal date. I doubt that US Military Finance and Accounting would be using a date system that is designed for astronomical observations.
The definition of Julian Date is well defined as described in the article, and the USNO is just following that.
Respectfully, it is not just Finance & Accounting and the Military.
They issue Desk Calenders to military and DOD Civilians that have the Julian Date on the top right hand corner.
1 JAN is Julian Date 1
31 JAN is Julian Date 31
1 Feb is Julian Date 32
31 DEC is Julian Date 365 (unless it is a leap year)
Perhaps they should throw out all of those calenders that they are using.
In the private sector they have Calendar Date to Julian Date Conversion Mappings.
If you use JD Edwards for instance and you want the corresponding calendar date, it does not translate to 12:00 Noon.
Same with SAP.
That is the ordinal date. As the article I posted a link to says:
"The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect."
That doesn't mean that using the term "Julian date" to refer to the ordinal date it is not in common usage, and that is what the OP, Military, JD Edwards, SAP, and so on are doing.
http://en.wikipedia.org/wiki/Ordinal_date
"An ordinal date is a calendar date typically consisting of a year and a day of year ranging between 1 and 366 (starting on January 1), though year may sometimes be omitted. The two numbers can be formatted as YYYY-DDD to comply with the ISO 8601 ordinal date format."
In addition, Julian date is also used to refer to dates on the Julian calendar, but that is not in widespread use, except in some Orthodox churches.
Plenty of confusion to go around.
It may sound confusing but generally the Julian Date translates to the day of the calendar year(1-365).
Have you worked anywhere where this is not the case?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2011 at 3:53 pm
Most systems I have worked do not use ordinal date; I personally don't see much value in it.
However, I have worked on applications that used the ordinal date (Julian date) and also worked with ones that use the astronomical Julian date.
I have also worked with systems that used the Modified Julian Date, another astronomical dating system that starts at midnight on 1858-11-17. The Modified Julian Date is the base date in the VMS operating system, so you sometimes see it used in systems that originated in that environment. It is a way to provide a single, increasing, absolute number for days, and is more convenient than the Julian date because it lines up with the calendar date at midnight and it is a smaller number.
Modified Julian Date = Julian Date - 2400000.5
Example: 2011-05-20 00:00:00 = Modified Julian Date 55701 = Julian Date 2455701.500000
I included all three forms in the Date Table Function that I posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Ordinal Date = column YEAR_DAY_OF_YEAR
Julian Date = column JULIAN_DATE
Modified Julian Date = column MODIFIED_JULIAN_DATE
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply