Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #5805 > unrolled thread
| Started by | Frans Wauters <info@wautersit.com> |
|---|---|
| First post | 2013-09-05 06:39 -0700 |
| Last post | 2013-09-06 10:07 -0500 |
| Articles | 3 — 3 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
How to work best with date/datetime/timestamp fields in VO2ADO ? Frans Wauters <info@wautersit.com> - 2013-09-05 06:39 -0700
Re: How to work best with date/datetime/timestamp fields in VO2ADO ? "Wolfgang Riedmann" <wriedmann@gmail.com> - 2013-09-05 16:06 +0200
Re: How to work best with date/datetime/timestamp fields in VO2ADO ? "Willie Moore" <williem@wmconsulting.com> - 2013-09-06 10:07 -0500
| From | Frans Wauters <info@wautersit.com> |
|---|---|
| Date | 2013-09-05 06:39 -0700 |
| Subject | How to work best with date/datetime/timestamp fields in VO2ADO ? |
| Message-ID | <c350aa1a-f6c0-4cdf-b205-f1d27ca5158a@googlegroups.com> |
How to work best with date/datetime/timestamp fields in VO2ADO ? At first, an MS SQL server returns a timestamp as something like this: 'Sep 5 2013 10:10:07:947PM' but timestamp is not a working date field. It’s more a representation of a number, it is in fact more a counter. In the later SQL versions is it renamed/called a RowVersion. http://msdn.microsoft.com/en-us/library/ms182776.aspx. As this is not the topic for this discussion, it is interesting to know that with every insert or update this field is updated. VALTYPE( oAdoServer:FIELDGET( #TimeStampField ) ) -> ‘M’ Qout() value like: ‘0x00000000000007E3’ Decimal: 2019. Does not make sense for “Time”-Stamp. :: via ODBC, it seems to give me an empty string :: ODBC struct = STRING, lenght: 8 Next. There are interesting fields: DATE and DATETIME and DATETIME2. First: DATETIME versus DATETIME2. With MS SQL 2008, DATETIME2 is introduced. This field is more accurate and can hold more dates. Date ranges are: DATETIME -> 1753-01-01 through 9999-12-31 DATETIME2 -> 0001-01-01 through 9999-12-31 SetDateCountry( BRITISH ) + AdoDateTimeAsDate( .T. ) gives a: Qout() value like: ‘05/09/2013’ oAdoServer:DBStruct gives it a type ‘D’ VALTYPE( oAdoServer:FIELDGET( #TimeDateField ) ) -> ‘D’ :: via ODBC, it gives a ‘2013-09-05 10:15:55.000’ :: ODBC struct = STRING, lenght: 48 SetDateCountry( BRITISH ) + AdoDateTimeAsDate( .F. ) gives a: Qout() value like: ‘05/09/2013 10:15:55’ oAdoServer:DBStruct gives it a type ‘D’ VALTYPE( oAdoServer:FIELDGET( #TimeDateField ) ) -> ‘O’ With VO2ADO you can convert it into 2 parts using the AdoDateTime class. Hence, you NEED to set AdoDateTimeAsDate() to FALSE, otherwise it fails badly! LOCAL oAdoDateTime AS AdoDateTime ... AdoDateTimeAsDate( FALSE ) ? "AdoDateTimeAsDate() => FALSE" ? " DateTimeField DATETIME ->", oAdoServer:FIELDGET( #DateTimeField ) oAdoDateTime := oAdoServer:FIELDGET( #DateTimeField ) ? " oAdoDateTime:DateVal ->", oAdoDateTime:DateVal ? " oAdoDateTime:TimeString ->", oAdoDateTime:TimeString And at last the DATE field. As this is more likely comparative with the VO DATE field, it seems not and present itself as a STRING. Range: DATE 1753-01-01 through 9999-12-31 Qout() value like: ‘05/09/2013’ VALTYPE( oAdoServer:FIELDGET( #DateField ) ) -> ‘C’ oAdoServer:DBStruct gives it a type ‘C’ :: via ODBC, it gives a ‘2013-09-05’ :: ODBC struct = STRING, lenght: 10 ODBC remark: The values are in format of the SQL server: DateTime: YYYY-MM-DD HH:MM:SS.hhh Date: YYYY-MM-DD --- Basically, using VO2ADO and setting AdoDateTimeAsDate( TRUE ) you have a more or less a compatible VO DATE type. Only you have to change all SQL DATE fields in the database to SQL DATETIME, and waste extra and unused space in the SQL database. Well I’m maybe a bit-fucker now? It is not that other developers do that, that I have to follow; I don’t want to jump of a cliff (merely because I don’t have a parachute)! I think it has to do with the limited DATE range of the SQL DATE field. Still; I have a lot of questions: (1) What to do with other programs that use DATE and DATETIME. Can you swap these fields from DATE to DATETIME without interrupting other, existing, 3rth party programs? (2) When we all use DATETIME fields and a customer wants in a browser using multiple dates in one row, the first in a DD/MM/YYYY format and the other in DD/MM/YYYY HH:MM:SS, how do you play with AdoDateTimeAsDate()? (3) I like the way DATETIME is used in VO2ADO, but why not for the DATE field in the SQL server? The name says it selves. I guess that it has to be because the SQL DATE range is smaller that the VO DATE field. (4) There are differences in table structures with the structure used by SQL server, VO2ADO and ODBC. Date type fields I have discussed, but example: Created field: [TotalStock] numeric not null default 0, Created by MS SQL as: TotalStock (Numeric(18,0), not null)) VO2ADO BD Struct = N, 19, 0 via OCBC DB Struct = N, 20, 0 Using information_schema.columns = N, 18, 0 <---- 18 ! Do we have a ‘number to big to fit in field’ overflow problem ? Kind regards, Frans
[toc] | [next] | [standalone]
| From | "Wolfgang Riedmann" <wriedmann@gmail.com> |
|---|---|
| Date | 2013-09-05 16:06 +0200 |
| Message-ID | <b8rhfpFraufU1@mid.individual.net> |
| In reply to | #5805 |
Hi Frans, > How to work best with date/datetime/timestamp fields in VO2ADO ? what about the AdoDateTime class? if you set AdoDateTimeAsDate(FALSE), the VO2ADO should return AdoDateTime objects. Wolfgang --
[toc] | [prev] | [next] | [standalone]
| From | "Willie Moore" <williem@wmconsulting.com> |
|---|---|
| Date | 2013-09-06 10:07 -0500 |
| Message-ID | <l0cr2q$3gk$1@speranza.aioe.org> |
| In reply to | #5806 |
Wolfgang, That is exactly how I did it with my VO2ADO program. The ADODataTime objects work great. Regards, Willie Vulcan VIP
[toc] | [prev] | [standalone]
Back to top | Article view | comp.lang.clipper.visual-objects
csiph-web