Tririga dates and SQL and MS Access

[Lloyd]

Some thoughts to share on Tririga dates and SQL and MS Access
(I repeated this from a post I answered to a separate post)

TRIRIGA DATES
Pronounced "WEIRD"
A couple of notes in using SQL and Access with Tririga Dates
Tririga uses a start date of 1-1-1970 as their base year
Dates are calculated as the time in days since then.
The number is stored in a numeric(32) field
A Date looks like: 1527742800000 or 619419600000
To make a date out of it see below.

Using SQL:

select
to_char(to_date('01-JAN-1970','DD-MON-YYYY') + ( TheDateFieldName /
(1000 * 60 * 60 * 24) ), 'DD-MON-YYYY HH24:MI:SS') "OutputColumnName"
from TheTABLEName ;

SELECT
A.TRIIDTX,
to_char(to_date('01-JAN-1970','DD-MON-YYYY') + ( A.TRISTARTDA/
(86400000) ),'mm/dd/yyyy HH24:MI:SS') "EFFECTIVE",
A.HHSREGIONTX, A.TRIADDRESSTX, A.TRIZIPPOSTALTX, A.TRICITYTX,
A.TRISTATEPROVTX, substr(A.HHSCOUNTYLI,7)
FROM T_TRIREALESTATECONTRACT A
WHERE A.triStatusCL NOT IN('Retired','Upload Error','Template',
'History', 'Deleted')

MS ACCESS

Here are 3 ACCESS date functions for Tririga Dates
You can copy and paste
' ** Tririga Date Functions
' ** A Date looks like: 1527742800000 or 619419600000
' ** Tririga uses a start date of 1-1-1970 as their base year
' ** Dates are calculated as the time in days since then.
' ** The number is stored in a numeric(32) field
' ** If you link in a Tririga table
' ** Access will not recognize the length so reads it as a text field
' ** Three Access functions:
' ** TrigDate: one with minutes in format mm/dd/yyyy h:nn:ss
' ** TrigDateM: one with minutes at 0 in format mm/dd/yyyy 0:00:00
' ** TrigDateD: one with no minutes in format mm/dd/yyyy
' ** Locate the functions in an accessible module and call or imply
them
' ** USE: TheDate = TrigDate
(t_triRealEstateContract.hhsEffectiveDate)
' ** might equal 01/21/2010 10:21:46
' ** OR: TheDate = TrigDateD
(t_triRealEstateContract.hhsEffectiveDate)
' ** might equal 01/21/2010
' **

Public Function TrigDate(strIn) As String
TrigDate = Format(DateValue("01-JAN-1970") + (Val([strIn]) /
86400000),
"mm/dd/yyyy h:nn:ss")
End Function

Public Function TrigDateM(strIn) As String
TrigDateM = Format(DateValue("01-JAN-1970") + Round(Val([strIn]) /
86400000), "mm/dd/yyyy 0:00:00")
End Function

Public Function TrigDateD(strIn) As String
TrigDateD = Format(DateValue("01-JAN-1970") + (Val([strIn]) /
86400000),
"mm/dd/yyyy")
End Function

It is all just in fun. We are all rich so we do this stuff for
entertainment!!!!
Lloyd

[Neha]

Hi,

I am very new to Tririga and am facing issue with duration field. how
are they stored in backend. I saw that when the duration is till weeks
it is stored normally like 7*24*60*60, But as sson as we give months
or above in duration field the number increases drastically.

Any help is highly appreciated!!

--Neha

[Eric]

The duration format is:

(100000000000000 * ((years * 12) + months)) +
(weeks * 604800000) + (days * 86400000) + (hours * 3600000) + (minutes
* 60000) + (seconds * 1000) + milliseconds

or basically

(100000000000000 * (months and years in months)) +
(everything else in milliseconds)

durations roll over this way in the application as well; i.e. if you
enter in 24 days, it will roll up to 3 weeks and 1 day, but if you
enter 240 days it will give you 34 weeks and 2 days (instead of
rolling to months and/or years). The reason being that months and
years are of variable duration, so there's no direct way to roll them
any higher without knowing your starting date. Months will roll up to
years though (since a year is always 12 months, even though it might
be 365 or 366 days etc.).

[Neha]

Thanks Eric!!

but there is again a problem with logic when i applied it in Pervasive
Data Integrator. Actually we use this tool to to perform data loading
in our application. Now in order to upload duration fields the value
goes too long when duration is in years and value that gets generated
after calculation in XML is in negative . Do you have any ideas
regarding this tool or how to cater this issue.

--Neha

> > > Lloyd- Hide quoted text -
>
> - Show quoted text -

[Neha]

the logic that i applied was :
estimate=null

If Not IsNull(Fields("YEARS") ) then
y =Fields("YEARS")
estimate = y*1200000000000
End if

If Not IsNull(Fields("MONTHS")) then
mon = Fields("MONTHS")
If Not IsNull(estimate) then
estimate=estimate+mon*100000000000
Else
estimate=mon*100000000000
End if
End if

If Not IsNull(Fields("WEEKS") ) then
wee = Fields("WEEKS")
If Not IsNull(estimate) then
estimate=estimate+wee*7*24*60*60
Else
estimate=wee*7*24*60*60
End if
End if

If Not IsNull(Fields("DAYS") ) then
days = Fields("DAYS")
If Not IsNull(estimate) then
estimate=estimate+days*24*60*60
Else
estimate=days*24*60*60
End if
End if

If Not IsNull(Fields("EST_HOURS") ) then
hou = Fields("EST_HOURS")
If Not IsNull(estimate) then
estimate=estimate+hou*60*60
Else
estimate=hou*60*60
End if
End if

If Not IsNull(Fields("MINUTES") ) then
min = Fields("MINUTES")
If Not IsNull(estimate) then
estimate=estimate+min*60
Else
estimate=min*60
End if
End if

If Not IsNull(Fields("SECONDS") ) then
sec = Fields("SECONDS")
If Not IsNull(estimate) then
estimate=estimate+sec
Else
estimate=sec
End if
End if

estimate = estimate * 1000
estimate

On Jan 29, 2:33 pm, Eric <eric.gl...@gmail.com> wrote:

[pajamasw]

Lloyd, thanks for your insight on using MS Access to work with Tririga
data. I too use Access daily to read and in some very limited cases,
write. I am including two date functions I use that also handle the
time part.

Public Function ToTriDate(datDate_to_Convert As Variant) As Variant

ToTriDate = (DateDiff("s", "1/1/1970 00:00:00",
datDate_to_Convert) * 1000)

End Function

Public Function FromTriDate(datDate_to_Convert As Double) As Variant

If datDate_to_Convert < 0 Then
FromTriDate = Null
Else
' convert Tririga date number into variant date
' add number of seconds in date from tririga to 1/1/1970
Dim datTemp As Variant
datTemp = DateAdd("s", datDate_to_Convert / 1000, "1/1/1970
00:00:00")
FromTriDate = DateAdd("h", -6, datTemp) ' sub 6 hours to get
CST
End If

End Function

The function above does not automatically adjust for daylight saving
time. So twice a year I change it accordingly. I should really work on
that some day, just for fun as you say!

Paul