How to find 2 digit years in legacy Database.

classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

How to find 2 digit years in legacy Database.

Eric Naujock
Is there a simple way to detect 2 digit years stored in the 4D database. Much less be able to fix the 2 digit year and convert it to a proper 4 digit year. I Had a utility that would try to convert is using the following. The code runs on 4D v15.2

C_DATE($1;$0)

If (Count parameters=1)
$0:=Date(String($1;ISO date GMT))
Else
$0:=!00-00-00!
End if


-----------------------------------------------------------------------

MacCafe
4405 Talmadge  St.
Toledo, OH 43623
Eric Naujock  -  ACSA 10.2, 10.3, 10.4 Apple - ACTC 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
http://www.mac-cafe.com
email: e <mailto:[hidden email]>[hidden email] <mailto:[hidden email]>
AOL IM: erlic



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Koen Van Hooreweghe
Hi Eric,

IMHO this question does not makes sense. Dates are not stored with 2, 4 or whatever number of year digits. Dates are stored as... euhm... well...: dates!

I guess you might have problems caused by imported dates from a text source which did not include the century info. This way you can have dates in the year 16 instead of 2016.
Those should be easily found with a simple query like (from the top of my head):

//to be OS date settings independent:
$date:=!0!//4d method editor will expand
$date:=add to date($date;1000;1;1)//or whatever year you think will be incorrect, in this case everything before januari first 1000
query([myTable];[myTable]myDate < $date)

HTH
Koen

Op 25-aug.-2016, om 16:22 heeft Eric Naujock <[hidden email]> het volgende geschreven:

> Is there a simple way to detect 2 digit years stored in the 4D database. Much less be able to fix the 2 digit year and convert it to a proper 4 digit year. I Had a utility that would try to convert is using the following. The code runs on 4D v15.2



--------------------
Compass bvba
Koen Van Hooreweghe
Kloosterstraat 65
9910 Knesselare
Belgium
tel +32 495 511.653

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Eric Naujock
This is where the joy of date and time come into play. 4D does not seem to define dates, instead its setup where a year can be anything between 0 and 32,767. Beyond stating that they seem to be saved as a month/Day/year format where the ranges can vary widely. The problem is that I have dates that are stored as two year dates, but do not show as a two year date. As a result of the fact that 4D was storing dates as 2 digit years I have people who are now showing up as being born in the year 2032 and having died in 1987. Things that make you say Hmmm. The problem is that the pivot date has moved and while its now defaulted at 2030 it didn't always do that. What I am looking for is a way to search for a 2 digit year to be able to flag them to be corrected to a proper 4 digit year. This way I can clean up the old Y2K nightmare that has never been able to be identified and caught.
-----------------------------------------------------------------------

MacCafe
4405 Talmadge  St.
Toledo, OH 43623
Eric Naujock  -  ACSA 10.2, 10.3, 10.4 Apple - ACTC 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
http://www.mac-cafe.com
email: e <mailto:[hidden email]>[hidden email] <mailto:[hidden email]>
AOL IM: erlic



> On Aug 25, 2016, at 10:55 AM, Koen Van Hooreweghe <[hidden email]> wrote:
>
> Hi Eric,
>
> IMHO this question does not makes sense. Dates are not stored with 2, 4 or whatever number of year digits. Dates are stored as... euhm... well...: dates!
>
> I guess you might have problems caused by imported dates from a text source which did not include the century info. This way you can have dates in the year 16 instead of 2016.
> Those should be easily found with a simple query like (from the top of my head):
>
> //to be OS date settings independent:
> $date:=!0!//4d method editor will expand
> $date:=add to date($date;1000;1;1)//or whatever year you think will be incorrect, in this case everything before januari first 1000
> query([myTable];[myTable]myDate < $date)
>
> HTH
> Koen
>
> Op 25-aug.-2016, om 16:22 heeft Eric Naujock <[hidden email]> het volgende geschreven:
>
>> Is there a simple way to detect 2 digit years stored in the 4D database. Much less be able to fix the 2 digit year and convert it to a proper 4 digit year. I Had a utility that would try to convert is using the following. The code runs on 4D v15.2
>
>
>
> --------------------
> Compass bvba
> Koen Van Hooreweghe
> Kloosterstraat 65
> 9910 Knesselare
> Belgium
> tel +32 495 511.653
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: How to find 2 digit years in legacy Database.

Timothy Penner
Hi Eric,

I think the pivot year has been 2030 for quite some time, at least since 4D v6.7, see here:
http://www.4d.com/docs/CMU/CMU00392.HTM 
"By default, 4D sets the century to be the 20th century and uses 30 as pivot year. "

I had assumed dates were stored in 4 digits and that the default century only affected data entry not stored values (i.e. it converts 2 digit dates to 4 digits; thus saving 4 digits to the database)...

Does changing the default century change the data you already have stored? Like if you set the default century to 25 does it show that this person died in 2532 and was born in 2487? If so, then you could probably set the default century to 25 and then query for all dates with a year >2400 and then reset the year for the records you find.

-Tim
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Eric Naujock
This database started out life in 4D back in 1985. As a result there is a a lot of data that dates prior to 6.7. As a result I have a lot of dates that are stored in 2 digit formats that have never been converted to 4 digit years. But now I am showing people born in 2032 and having died in 1987. This kind of messes up the statistics and makes the reporting fail miserably. What am in need of doing is finding the proper year and being to define it in mass for all the records in the database so that I have no 2 digit years.

        The code that I used as an example I thought would do it by using string to convert the date to an ISO format then using date to convert the string date back to a proper date. The difficulty I am running into is that I have a ton of 2 digit dates years in the system that I need to get fixed into 4 digit years and I am looking for a relatively safe an effective way to do this. Ideally I would like to be able to block the ability to just enter 2 digit years and require 4 digit years.

-----------------------------------------------------------------------

MacCafe
4405 Talmadge  St.
Toledo, OH 43623
Eric Naujock  -  ACSA 10.2, 10.3, 10.4 Apple - ACTC 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
http://www.mac-cafe.com
email: e <mailto:[hidden email]>[hidden email] <mailto:[hidden email]>
AOL IM: erlic



> On Aug 25, 2016, at 1:29 PM, Timothy Penner <[hidden email]> wrote:
>
> Hi Eric,
>
> I think the pivot year has been 2030 for quite some time, at least since 4D v6.7, see here:
> http://www.4d.com/docs/CMU/CMU00392.HTM 
> "By default, 4D sets the century to be the 20th century and uses 30 as pivot year. "
>
> I had assumed dates were stored in 4 digits and that the default century only affected data entry not stored values (i.e. it converts 2 digit dates to 4 digits; thus saving 4 digits to the database)...
>
> Does changing the default century change the data you already have stored? Like if you set the default century to 25 does it show that this person died in 2532 and was born in 2487? If so, then you could probably set the default century to 25 and then query for all dates with a year >2400 and then reset the year for the records you find.
>
> -Tim
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

KirkBrooks
Eric,
I work on a genealogy project from time to time. The best solution I've
found for managing dates across long time spans is to store them as
strings. Either

YYYYMMDD  (I used this for years)

or the ISO format

YYYY-MM-DD

​You would have to setup new fields for them and generate some utilities to
manage them (I'll share mine if you are interested). ​Has the advantage
of accommodating approximations (that's the genealogy thing). And it's easy
to append the time string as well.


​I've never had any performance issues with this, by the way. ​String
operations are pretty fast.

On Thu, Aug 25, 2016 at 10:51 AM, Eric Naujock <[hidden email]> wrote:

> What am in need of doing is finding the proper year and being to define it
> in mass for all the records in the database so that I have no 2 digit years.
>

--
Kirk Brooks
San Francisco, CA
=======================
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Peter Bozek-2
In reply to this post by Eric Naujock
On Thu, Aug 25, 2016 at 4:22 PM, Eric Naujock <[hidden email]> wrote:

> Is there a simple way to detect 2 digit years stored in the 4D database.
> Much less be able to fix the 2 digit year and convert it to a proper 4
> digit year. I Had a utility that would try to convert is using the
> following. The code runs on 4D v15.2
>
> C_DATE($1;$0)
>
> If (Count parameters=1)
> $0:=Date(String($1;ISO date GMT))
> Else
> $0:=!00-00-00!
> End if
>

This will not help. AFAIK, 4D uses three numbers to store date (day, month
and year.) Your problem is some of dates contain year in range 1 - 99
instead of 1901 - 1999.

What you need is rather something like (this is short idea, adjust it for
your needs)

C_DATE($1;$0)
C_LONGINT($year)

$year:=Year of($1)
Case of
  : ($year<15)
    $0:=Add to date($1;2000;0;0)
  : ($year<100)
    $0:=Add to date($1;1900;0;0)
Else
  $0:=$1
End if

--

Peter Bozek
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Re: How to find 2 digit years in legacy Database.

Tim Nevels-2
In reply to this post by Eric Naujock
On Aug 25, 2016, at 2:00 PM, Eric Naujock wrote:

> This database started out life in 4D back in 1985. As a result there is a a lot of data that dates prior to 6.7. As a result I have a lot of dates that are stored in 2 digit formats that have never been converted to 4 digit years. But now I am showing people born in 2032 and having died in 1987. This kind of messes up the statistics and makes the reporting fail miserably. What am in need of doing is finding the proper year and being to define it in mass for all the records in the database so that I have no 2 digit years.
>
> The code that I used as an example I thought would do it by using string to convert the date to an ISO format then using date to convert the string date back to a proper date. The difficulty I am running into is that I have a ton of 2 digit dates years in the system that I need to get fixed into 4 digit years and I am looking for a relatively safe an effective way to do this. Ideally I would like to be able to block the ability to just enter 2 digit years and require 4 digit years.

On Aug 25, 2016, at 2:00 PM, Peter Bozek wrote:

> This will not help. AFAIK, 4D uses three numbers to store date (day, month
> and year.) Your problem is some of dates contain year in range 1 - 99
> instead of 1901 - 1999.
>
> What you need is rather something like (this is short idea, adjust it for
> your needs)
>
> C_DATE($1;$0)
> C_LONGINT($year)
>
> $year:=Year of($1)
> Case of
>  : ($year<15)
>    $0:=Add to date($1;2000;0;0)
>  : ($year<100)
>    $0:=Add to date($1;1900;0;0)
> Else
>  $0:=$1
> End if

Peter is right, 4D has always stored dates internally as 3 “fields”. Each “field” is 2 bytes in size or an integer. Hence, maximum year value of 32,732.

4D also puts some constraints on the values allowed to be stored. Minimum year is 100, maximum year 32,767, Maximum month 12 and maximum day 31. But it also allows all 3 of these to be zero. And it won’ t allow you to store a date like February, 31 2016 which cannot exist in real life.  You can verify this in the debugger with code like this:

C_DATE($date_d)
$date_d:=Add to date(!00-00-00!;2016;2;31)
$date_d:=!2016-02-31!

Notice the debugger shows $date_d to be: March, 2 2016

I would do a little analysis of these bogus dates using the “Year of” function on the date field. If it returns values like 87 or 32 instead of 1987 or 2032 then you might be able to use Peter’s code above to fix them. The current documentation says it doesn’t allow years below 100.

http://doc.4d.com/4Dv15/4D/15.1/Data-Types.300-2685800.en.html

But just to be sure, and I always like to be sure, I did a quick test and found that the “Add to date” function appears to bypass documentation. You can store a date with a year of 10. This code will store a date with a year of 16. So the documentation is wrong about minimum year allowed!

C_DATE($date_d)
$date_d:=Add to date(Current date;-2000;0;0)
$year:=Year of($date_d)

If year is returning 4 digit years you may have to be creative and try to logically fix the dates. Example: if the year of the birth date or death date is greater than 2016, then you can be certain the you need to subtract 100 years to fix it. If the death year is before the birth year — like your example 1987 and 2032 — then you can logically assume the birth year needs 100 subtracted from it.

That’s all I can offer on how to clean up your data. Spend some time with “Add to date” and the “Year of” functions and see what values you are getting. That may help you figure out a logical way of fixing these bogus dates.

One last “good programming practice” thing is for any birthdate fields on forms always set the display format to show 4 digit years and set the entry filter to require entering 4 digit years. This can make obvious data entry errors immediately seen and corrected.

Tim

********************************************
Tim Nevels
Innovative Solutions
785-749-3444
[hidden email]
********************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Arnaud de Montard

> Le 25 août 2016 à 21:45, Tim Nevels <[hidden email]> a écrit :
>
>> [...]
>
> Peter is right, 4D has always stored dates internally as 3 “fields”. Each “field” is 2 bytes in size or an integer. Hence, maximum year value of 32,732.

Hi Tim,
since v11 I think this is wrong, see the number of bytes of some 4D fields:
<http://screencast.com/t/eRDzgw0BNZ9>
Both date and hour types are 8 bytes whereas if we where still in the before-v11-situation, date should be 6 (2*3) and hour should be 4 (same as a longint, which explains the selection to array(hourField;longintArray)).
This (added to some strange things happening in SQL queries on date fields, example <http://forums.4d.fr/Post/FR/16272092/0/0/>) makes me think that this 8 byte storage is a timestamp (date+hour), still not "revealed" by 4D for compatibility reasons. Pure speculation, of course.

--
Arnaud de Montard




**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Tim Nevels-2
In reply to this post by Eric Naujock
On Aug 27, 2016, at 2:00 PM, Arnaud de Montard wrote:

> since v11 I think this is wrong, see the number of bytes of some 4D fields:
> <http://screencast.com/t/eRDzgw0BNZ9>
> Both date and hour types are 8 bytes whereas if we where still in the before-v11-situation, date should be 6 (2*3) and hour should be 4 (same as a longint, which explains the selection to array(hourField;longintArray)).
> This (added to some strange things happening in SQL queries on date fields, example <http://forums.4d.fr/Post/FR/16272092/0/0/>) makes me think that this 8 byte storage is a timestamp (date+hour), still not "revealed" by 4D for compatibility reasons. Pure speculation, of course.

Very good catch Arnaud. I was not aware of the change in size for date and time fields. So now we have to wonder about the reason for the change in size. I’ll speculate too. :) Possible design decisions:

1. Faster and easier to read 8 bytes than to read 6 bytes, so read 8 and only use 6 to store the date in the same format as before as integers: 2 bytes for year, 2 bytes for month, 2 bytes for day.

2. Store a “timestamp” in the 8 bytes in some alpha format like YYYYMMDDHHMMSS and have 2 bytes left over. Sort of a compressed UTC type format.

3. Store a “timestamp” in the 8 bytes in the form of 2 longints. First one is the number of days since some epoch and second one for time.

That would give you a very large range of dates to be able to store. Say you started at Jan. 1, 0AD. Aug. 28, 2016 would only be 736,203 days. You could go crazy and say start at 10,000BC and go to 10,000AD and that’s only 7,304,724 days.  http://www.planetcalc.com/274/ 

In the second longint, store number of milliseconds in a day. That’s 86,400,000 milliseconds per day. Very good precision for storing a time value, and easy to store in a longint.

The problem with using a longint for a timestamp like unix does is you start at 00:00:00 on Jan 1, 1970, so you can’t easily store dates prior to that. And you can’t store values past 03:14:07 on Jan 19, 2038.

I’ll now include the 3 “magic characters" JPR in this post to trigger his email filter to flag this post as something he might be interested in reading. Being the author of DataAnalyzer I bet he knows the new 4D v11 date and time formats.

Do you know JPR?

Tim

********************************************
Tim Nevels
Innovative Solutions
785-749-3444
[hidden email]
********************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Benedict, Tom-2
In reply to this post by Eric Naujock
Tim Nevels writes:
>On Aug 27, 2016, at 2:00 PM, Arnaud de Montard wrote:
>
>> since v11 I think this is wrong, see the number of bytes of some 4D fields:
>> <http://screencast.com/t/eRDzgw0BNZ9>
>> Both date and hour types are 8 bytes whereas if we where still in the before-v11-situation, date should be 6 (2*3) and hour should be 4 (same as a longint, which explains the selection to array(hourField;longintArray)).
>> This (added to some strange things happening in SQL queries on date fields, example <http://forums.4d.fr/Post/FR/16272092/0/0/>) makes me think that this 8 byte storage is a timestamp (date+hour), still not "revealed" by 4D for compatibility reasons. Pure speculation, of course.

4D does support the datetime data type when the field is created using SQL commands. It will behave like a date field when referencing it with 4D commands, but since it actually contains the time part too, you can get a 'timestamp' by using CAST(myDateTimeField as VARCHAR) in your SQL select statement.

Tom Benedict
Optum

This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Chip Scheide
In reply to this post by Eric Naujock
I think you can change the pivot year.
If so -
change the pivot year to something that makes sense (00? or 99)
Then go and search for dates <= pivot year
then apply to the selection extracting and saving the date with an
explicit year (19xx I assume)

On Thu, 25 Aug 2016 13:51:27 -0400, Eric Naujock wrote:

> This database started out life in 4D back in 1985. As a result there
> is a a lot of data that dates prior to 6.7. As a result I have a lot
> of dates that are stored in 2 digit formats that have never been
> converted to 4 digit years. But now I am showing people born in 2032
> and having died in 1987. This kind of messes up the statistics and
> makes the reporting fail miserably. What am in need of doing is
> finding the proper year and being to define it in mass for all the
> records in the database so that I have no 2 digit years.
>
> The code that I used as an example I thought would do it by using
> string to convert the date to an ISO format then using date to
> convert the string date back to a proper date. The difficulty I am
> running into is that I have a ton of 2 digit dates years in the
> system that I need to get fixed into 4 digit years and I am looking
> for a relatively safe an effective way to do this. Ideally I would
> like to be able to block the ability to just enter 2 digit years and
> require 4 digit years.
>
> -----------------------------------------------------------------------
>
> MacCafe
> 4405 Talmadge  St.
> Toledo, OH 43623
> Eric Naujock  -  ACSA 10.2, 10.3, 10.4 Apple - ACTC 10.5, 10.6, 10.7,
> 10.8, 10.9, 10.10
> http://www.mac-cafe.com
> email: e <mailto:[hidden email]>[hidden email] <mailto:[hidden email]>
> AOL IM: erlic
>
>
>
>> On Aug 25, 2016, at 1:29 PM, Timothy Penner <[hidden email]> wrote:
>>
>> Hi Eric,
>>
>> I think the pivot year has been 2030 for quite some time, at least
>> since 4D v6.7, see here:
>> http://www.4d.com/docs/CMU/CMU00392.HTM 
>> "By default, 4D sets the century to be the 20th century and uses 30
>> as pivot year. "
>>
>> I had assumed dates were stored in 4 digits and that the default
>> century only affected data entry not stored values (i.e. it converts
>> 2 digit dates to 4 digits; thus saving 4 digits to the database)...
>>
>> Does changing the default century change the data you already have
>> stored? Like if you set the default century to 25 does it show that
>> this person died in 2532 and was born in 2487? If so, then you could
>> probably set the default century to 25 and then query for all dates
>> with a year >2400 and then reset the year for the records you find.
>>
>> -Tim
>> **********************************************************************
>> 4D Internet Users Group (4D iNUG)
>> FAQ:  http://lists.4d.com/faqnug.html
>> Archive:  http://lists.4d.com/archives.html
>> Options: http://lists.4d.com/mailman/options/4d_tech
>> Unsub:  mailto:[hidden email]
>> **********************************************************************
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Tim Nevels-2
In reply to this post by Benedict, Tom-2
On Aug 29, 2016, at 8:34 AM, Benedict, Tom <[hidden email]> wrote:

> 4D does support the datetime data type when the field is created using SQL commands. It will behave like a date field when referencing it with 4D commands, but since it actually contains the time part too, you can get a 'timestamp' by using CAST(myDateTimeField as VARCHAR) in your SQL select statement.


I have seen the TIMESTAMP column type in SQL and I’d like to use it. But how do you put a value into it. The following SQL code does not work:

$timeStamp_t:=String(Current date;ISO Date GMT;Current time)

INSERT INTO TheTable (TheTimeStamp) VALUES (:$timeStamp_t)

What is is the magic to put a 4D time stamp text var into a SQL TIMESTAMP column?

Tim

********************************************
Tim Nevels
Innovative Solutions
785-749-3444
[hidden email]
********************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: How to find 2 digit years in legacy Database.

Timothy Penner
Hi Tim,

> What is is the magic to put a 4D time stamp text var into a SQL TIMESTAMP column?

Here are some examples:

Inserting:
=======

Begin SQL
INSERT INTO Table_1 (Field_2)
VALUES (CURRENT_TIMESTAMP());
End SQL


Getting the data back:
=================

Using Cast:

C_TEXT($text1)
Begin SQL
SELECT CAST(Field_2 AS TEXT)
FROM Table_1
WHERE ID = 1
INTO :$text1;
End SQL
// theText = 8/29/2016 10:55

Using date and time vars:

C_DATE($dd)
C_TIME($tt)
Begin SQL
SELECT Field_2, Field_2
FROM Table_1
WHERE ID = 1
INTO :$dd, :$tt;
End SQL
  // $dd = 8/29/2016
// $tt = 10:55:31



Kind Regards,

Tim PENNER
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: How to find 2 digit years in legacy Database.

Timothy Penner
In reply to this post by Tim Nevels-2
Also, if you want to build a timestamp in 4D here is the format:

http://doc.4d.com/4Dv15R4/4D/15-R4/Principles-for-integrating-4D-and-the-4D-SQL-engine.300-2818873.en.html#1352502

Inserting
=======

Begin SQL
INSERT INTO Table_1 (Field_2)
VALUES ({ ts '1998-05-02 01:23:56.123' });
End SQL

Getting the data back is the same as my last email:
======================================

C_TEXT($text1)
Begin SQL
SELECT CAST(Field_2 AS TEXT)
FROM Table_1
WHERE ID = 3
INTO :$text1;
End SQL
  // $text1 = 5/2/98 1:23


C_DATE($dd)
C_TIME($tt)
Begin SQL
SELECT Field_2, Field_2
FROM Table_1
WHERE ID = 3
INTO :$dd, :$tt;
End SQL
  // $dd = 05/02/1998
  // $tt = 01:23:56

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: How to find 2 digit years in legacy Database.

Timothy Penner
Here are two more ways to insert timestamps into 4 via SQLD:

Massaging the data:

$timeStamp_t:=String(Current date;ISO date GMT;Current time)  
$timeStamp_t:=Replace string($timeStamp_t;"T";" ")  // remove T from middle of timestamp
$timeStamp_t:=Replace string($timeStamp_t;"Z";"")  // remove Z from end of timestamp
Begin SQL
INSERT INTO Table_1 (Field_2)
VALUES (:$timeStamp_t);
End SQL

Concatenation with TS syntax and execute immediate:

$timeStamp_t:=String(Current date;ISO date GMT;Current time)  
$timeStamp_t:=Replace string($timeStamp_t;"T";" ")  // remove T from middle of timestamp
$timeStamp_t:=Replace string($timeStamp_t;"Z";"")  // remove Z from end of timestamp
$sql:="INSERT INTO Table_1 (Field_2) "
$sql:=$sql+"VALUES ({ ts '"+$timeStamp_t +"' });"
Begin SQL
EXECUTE IMMEDIATE :$sql;
End SQL



-----Original Message-----
From: 4D_Tech [mailto:[hidden email]] On Behalf Of Timothy Penner
Sent: Monday, August 29, 2016 11:04 AM
To: 4D iNug Technical
Subject: RE: How to find 2 digit years in legacy Database.

Also, if you want to build a timestamp in 4D here is the format:

http://doc.4d.com/4Dv15R4/4D/15-R4/Principles-for-integrating-4D-and-the-4D-SQL-engine.300-2818873.en.html#1352502

Inserting
=======

Begin SQL
INSERT INTO Table_1 (Field_2)
VALUES ({ ts '1998-05-02 01:23:56.123' }); End SQL

Getting the data back is the same as my last email:
======================================

C_TEXT($text1)
Begin SQL
SELECT CAST(Field_2 AS TEXT)
FROM Table_1
WHERE ID = 3
INTO :$text1;
End SQL
  // $text1 = 5/2/98 1:23


C_DATE($dd)
C_TIME($tt)
Begin SQL
SELECT Field_2, Field_2
FROM Table_1
WHERE ID = 3
INTO :$dd, :$tt;
End SQL
  // $dd = 05/02/1998
  // $tt = 01:23:56

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: How to find 2 digit years in legacy Database.

Tim Nevels-2
In reply to this post by Eric Naujock
On Aug 29, 2016, at 2:00 PM, Timothy Penner wrote:

> Here are two more ways to insert timestamps into 4 via SQLD:
>
> Massaging the data:
>
> $timeStamp_t:=String(Current date;ISO date GMT;Current time)  
> $timeStamp_t:=Replace string($timeStamp_t;"T";" ")  // remove T from middle of timestamp
> $timeStamp_t:=Replace string($timeStamp_t;"Z";"")  // remove Z from end of timestamp
> Begin SQL
> INSERT INTO Table_1 (Field_2)
> VALUES (:$timeStamp_t);
> End SQL

Thanks Tim. This is what I was looking for.

The 4D SQL engine is so strict. I wish it would recognize the timestamp format that 4D itself provides via the “String” command. But wishing does no good, and feature requests do only slightly more good.

At least now I know how to massage a 4D timestamp into a format the 4D SQL engine accepts.

Tim

********************************************
Tim Nevels
Innovative Solutions
785-749-3444
[hidden email]
********************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Arnaud de Montard
In reply to this post by Benedict, Tom-2

> Le 29 août 2016 à 15:34, Benedict, Tom <[hidden email]> a écrit :
>
> 4D does support the datetime data type when the field is created using SQL commands. [...]

Hi Tom,
if a date field is created with legacy tools, can it be used it as a datetime?
And an hour field?

--
Arnaud de Montard


**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Eric Naujock
Actually this issue is related in that I have numerous people who have used a 2 digit year in their data entry and now they have a person who died in 16 and were born in 28 which makes them -12 years old right?  According the the 4D date format that is right.

At least based on what I am seeing in 4D with the pivot dates that is right. The question is how to force people who are entering dates to put in a 4 digit year, and be able to find the 2 digit years and be able to correct them to a proper 4 digit year. Without messing up properly entered 4 digit years.

-----------------------------------------------------------------------

MacCafe
4405 Talmadge  St.
Toledo, OH 43623
Eric Naujock  -  ACSA 10.2, 10.3, 10.4 Apple - ACTC 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
http://www.mac-cafe.com
email: e <mailto:[hidden email]>[hidden email] <mailto:[hidden email]>
AOL IM: erlic



> On Aug 31, 2016, at 4:33 AM, Arnaud de Montard <[hidden email]> wrote:
>
>
>> Le 29 août 2016 à 15:34, Benedict, Tom <[hidden email]> a écrit :
>>
>> 4D does support the datetime data type when the field is created using SQL commands. [...]
>
> Hi Tom,
> if a date field is created with legacy tools, can it be used it as a datetime?
> And an hour field?
>
> --
> Arnaud de Montard
>
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: How to find 2 digit years in legacy Database.

Benedict, Tom-2
In reply to this post by Eric Naujock
Hi Eric,



>Actually this issue is related in that I have numerous people who have used a 2 digit

>year in their data entry and now they have a person who died in 16 and were

>born in 28 which makes them -12 years old right?  According the the 4D date format that is right.



>At least based on what I am seeing in 4D with the pivot dates that is right. The

>question is how to force people who are entering dates to put in a 4 digit year,

>and be able to find the 2 digit years and be able to correct them to a proper 4 digit year.

>Without messing up properly entered 4 digit years.



With birthdates I don't think there is any question that 4 digits are mandatory. Pivot years don't help. It's gotta be totally unambiguous.



Tom Benedict

Optum

This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
12