New post
Question
· Mar 13, 2023

? Date before Dec.1840 ? Negative $H(orolog) ?

#Other

To most of us it's almost dogmatic that $H can't be negative!

This is correct concerning the COS Special Variable $HOROLOG.
No doubt. It's the presentation of the actual date & time.
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

For dates before 1841 it's suggested to use Julian date before 1841.
Good for calculations.

But what about values in $H_format ?
Conversion functions $ZD(), $ZDT(), $ZDH(), $ZDTH() and  
for SQL  $system.SQL.TOCHAR() and $system.SQL.TODATE()
take  care of the conversion to human readable date. You know this.
But Julian date as input isn't welcome there. sad

Though have you ever read in detail what the parameter mindate in $ZD* functions does?
It allows you to use parameter in $H_format  with negative date value!
So:

WRITE $ZDATEH("02/29/1804",1,,,,,-14974)
-13455
WRITE $ZD(-7,,,,,,-366)
24/12/1840

Well, if you think it's quite an effort to change all date conversions in your programs I agree with you!
If mindate is empty the value for the smallest date allowed is taken from your NLS definitions.
 

SET x=##class(%SYS.NLS.Format).SetFormatItem("DateMinimum",-714)

and all $ZD*() + $system.SQL.TO*  function follow the new limit without any further change.

WARNINGS:
1) The smallest accepted value for mindate is -672045  :==> 01/01/0001 

2) As with other DBs this works as a Proleptic Gregorian Calendar.
Which means it calculates backwards as if we always had the Greogrian date since beginning and the lost days in October 1582 and other adjustments are ignored. Therfore dates / weekdays before Oct.15, 1528 are questionable.
eg. Calculating what day of the week Columbus reached on  Oktober 12, 1492 in America might be incorrect.wink

Discussion (4)4
Log in or sign up to continue
David Van De Griek · Aug 13, 2017

$system.SQL.TODATE() converts a readable format into a logical %Date value (+$Horolog).

$system.SQL.TODATE() does support converting from Julian date to %Date if you use the "J" format.  However, there is one issue; the value is returned in YYYY-MM-DD format for dates prior to 1841-01-01.  I will make a note to correct this.

 

USER>for Julian=2393465:1:2393475 w !,$SYSTEM.SQL.TODATE(Julian,"J")
1840-12-25
1840-12-26
1840-12-27
1840-12-28
1840-12-29
1840-12-30
0
1
2
3
4

 

"J"ulain format is also supported for $SYSTEM.SQL.TOCHAR():

USER>set string="2017-08-13" write !,$SYSTEM.SQL.TOCHAR(string,"J")   
​2457979
1 0
Steven Hobbs · Sep 1, 2017

In regard to:  "Calculating what day of the week Columbus reached on  Oktober 12, 1492 in America might be incorrect."  Columbus would have been using the "Julian Calendar", which reckons dates quite differently than using the "Julian Day Number", despite the similarity of the names.  Note that Julian Day number changes at Noon UTC rather than changing at Midnight local time where the Gregorian calendar and Julian Calendar assume the date changes.  October 12, 1492 (Julian Calendar) is October 21, 1492 (Gregorian), a 9 day difference, since there was a 10 day difference when the Gregorian calendar started on October 15, 1582 (October 5, 1582 Julian Calendar) and February, 1500 was a leap year in Julian Calendar but not in the Gregorian Calendar.  Both the Julian Calendar and the Gregorian Calendar (and the Islamic and Hebrew calendars) would agree that the first Columbus day was a Friday.  I.e., whenever any calendar adds a leap day/month or skips/adds days to switch between calendars, the days of the week just change by 1 normal day when we switch from one sunrise to the next.  Religions and countries may argue over what year of the calendar it is and what month of the year it is and what day of the month it is but there is much less argument over which day of the week it is.

4 0
Robert Cemper · Sep 1, 2017

Great explanation of the issue. Thanks!
So we have an nice example what Proleptic Gregorian Calendar used for $H calculation means:

 

write $zd($zdh("1492-10-12",3,,,,,-600000)#7,11)
Wed

 

And that's definitely not correct as you demonstrated very precisely.
But is common use in most programming and DB systems. 

But the date as such is questionable for 2 reasons

  • There is a 5..6 hours time gap between Spain and the Caribean sea 
  • At the and of the middle age every kingdom and smaller typically dated their documents
    by the years their actual king was in power. A common date as we know was not at all in place.

So Oct.12 is most likely a date back calculated by historians hundred years later . 
So we should interpret this date as an common agreed convention that by luck was Friday.

Thanks again for the contribution.

 

2 0