(not only empty) datetime literals

This forum is meant for questions about the Visual FoxPro Language support in X#.

Post Reply
atlopes
Posts: 83
Joined: Sat Sep 07, 2019 11:43 am
Location: Portugal

(not only empty) datetime literals

Post by atlopes »

Dear All,

X# 2.7 is not accepting VFP's empty datetime literals and is also interpreting Ante- and Post- Meridian in a different manner.

In VFP

Code: Select all

? {^2021-07-04 12:30:00PM}, {^2021-07-04 12:30:00AM}
* displays: 04/07/2021 12:30:00 04/07/2021 00:30:00
In X#

Code: Select all

? {^2021-07-04 12:30:00PM}, {^2021-07-04 12:30:00AM}
* displays: 04/07/2021 12:30:00 04/07/2021 12:30:00
Empty date and datetime strings can follow this pattern, after removing all CHR(32):

Code: Select all

{([/.-]{0,2})?(T{0,2}:{0,2}((a|A|p|P)(m|M)?)?)?}
The simultaneous capture of the Group 1 and Group 2 (that is, the date part and the time part) will result in an empty datetime. All other matches will result in an empty date literal.
User avatar
robert
Posts: 4551
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

(not only empty) datetime literals

Post by robert »

António ,

Thanks for the suggestions. So 12:30 AM is not interpreted correctly. I must say that I find the US notation VERY confusing (why is that not 00:30:00 AM ?).
An empty DateTime will be difficult. We wiil have to convert these to either a DateTime.MinValue or an uninitialized Nullable Datetime .

Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
atlopes
Posts: 83
Joined: Sat Sep 07, 2019 11:43 am
Location: Portugal

(not only empty) datetime literals

Post by atlopes »

I agree with you, Robert; it's confusing. In a 12-hour notation system, we shouldn't be allowed to use the 12th hour, in the same way that we can't use the 24th hour in a 24-hour notation system.

Another question regarding the datetime literals I forgot to mention: VFP follows the ISO-8601 pattern to indicate a time part that may be preceded by a T. So, {^2021-04-08T11:19:00} should not trigger a compile error.

As for the "empty" datetimes, I understand that it may pose a problem. I would prefer an uninitialized Nullable, but that's just me. How is X# addressing empty dates and datetimes in dbf tables?
info@task.si
Posts: 31
Joined: Mon Nov 02, 2015 2:08 pm

(not only empty) datetime literals

Post by info@task.si »

Dear all !

If we agree that sometimes we need empty values for a dates, than in a real world this fact can be very painfull.
In my case I got a problems with null dates in my SQL tables. My development environment is now .NET, X# Core dialect.
For a various purposes I am reading a data and saving them in a List structure.
There were no errors at compile time, but at a run time programme crashes. I found that a problem were empty values in some types of fields. I spent some hours solving empty (null) dates.
Here is my solution: (DatiTime literals)
....
Method XS_LoadDataFromMSSQL(sConnStr AS STRING) as void
LOCAL oConn AS System.Data.SqlClient.SqlConnection
LOCAL oCommand := NULL AS System.Data.SqlClient.SqlCommand
LOCAL myDate AS DateTime
LOCAL oDT := DataTable{"MySQLTable"} as DataTable
LOCAL oData := List<SQL_Data>{} as List<SQL_Data>

oConn := OpenConnection(sConnStr)
TRY
oCommand := oConn:CreateCommand()
oCommand:CommandText := "Select PRICE,PRICE_DATE from MySQLTable"
oConn:Open()
¸ self:oDT := DataTable{"MySQLTable"}
foreach row as DataRow in self:oDT:Rows
// testing null values (empty dates)
if row["PRICE_DATE"] == DBNull.Value
// it works --> set value 01.01.0001
// myDate := default(DateTime)

// or put X# DateTime Literals
myDate := {^0001-01-01 00:00:00}
else
myDate := Convert.ToDateTime(row["PRICE_DATE"])
endif
oData:Add(SQL_Data{} Price := Convert.ToDouble(row["PRICE"]),PriceDate := myDate })
next

Hope somebody will help.
Regards, Andrej
User avatar
robert
Posts: 4551
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

(not only empty) datetime literals

Post by robert »

Andrej,
info@task.si wrote: myDate := {^0001-01-01 00:00:00}
This is the same as MyDate := DateTime.MinValue

Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
info@task.si
Posts: 31
Joined: Mon Nov 02, 2015 2:08 pm

(not only empty) datetime literals

Post by info@task.si »

Hi Robert !
Thanks, good to know .
Andrej
mainhatten
Posts: 200
Joined: Wed Oct 09, 2019 6:51 pm

(not only empty) datetime literals

Post by mainhatten »

robert wrote:An empty DateTime will be difficult. We wiil have to convert these to either a DateTime.MinValue or an uninitialized Nullable Datetime .
Hi Robert,
problem might be compounded be previous upsize efforts: ^1899.12.31 is/was often inserted via ODBC or OleDB into the C/S datastore. Some opted to update values with .null. in advance of upsizing, others felt the need to differentiate between BLANK (=MinValue) and .null.
Perhaps not hardcoding, but offering x# conversion functions to overwrite on app level depending on backend and other necessities (but defaulting to DateTime.MinValue) is better, as it allows explicit intent or backend-specific MinValues to be followed.
edit:
at

my 0.22€
thomas
Post Reply