Hi everyone.
I’m using Xojo 2019r1.1 on Windows 11.
It is known that in Windows it is not possible to set a date before January 1, 1601.
I have to store writers data in a table, including the date of birth: when I come across Dante Alighieri I should store May 13, 1265, so the only way I’ve found is to define the field as a string instead of a date. Does anyone know of any alternative methods?
Thanks, good coding!
You could use an Int64 to store the number of seconds or days from 01/01/01. You would, of course, need to write some code to convert back and forth between the integer and a human readable date.
Not positive about this but in the 80’s I used a Julian date routine that could go back before bc. You might be able to find something like this on the web. The version I used was written in quickbasic but would be easy to convert to Xojo.
1 Like
Without knowing the details of this issue, unless you have to do any calculations on this value, e.g., calculate the current age or age at death, it seems like a string in the SQL format is your best option.
And even if you do have to do calculations, that should be easy enough to code. Consider creating a class that handles this.
Or upgrade to the latest Xojo and use the DateTime class that (I think) doesn’t have this limitation.
Thank you all.
I will take your suggestions into consideration.
Nedi
The DateTime class was added in 2019r2 if that is available to you?
Hi Emile, I am using Xojo 2019r1.1
Too bad !
Hi Ian,
following your post I tried using Xojo 2019r2.1 (my license is ok with this release).
I have a SQL Server database which contains the table “Authors”; this table has a field (AU_BirthDate) defined as Date (in SQL Server this type can store dates from 0001-01-01 to 9999-12-31).
One record has the AU_BirthDate set to 1265-05-13.
I read this record using a RowSet and a ODBC connection, but the date is 1601-01-13, as it was a Date datatype.
What am I doing wrong?
You’re using DateTimeValue to pull the date from the recordset?
Can you create a DateTime with that date, separate from the database? Ie.,
var d as new DateTime(1265, 5, 13)
If you can, then it may be an issue with ODBC. Do you have access to MonkeyBread?
Hi Tim, you are right: I have just found out that using ODBC plugin it doesn’t work, but using MSSQLServer plugin everything is ok.
Now I try with MBS plugin…
The MBS plugin doesn’t work…perhaps I do something wrong…I’ll ask Christian for help.
Hi Nedi,
In order to get this right you need to divorce your view of SQL structure from the Xojo Classes. Just because a field is a Date, and has no time component, doesn’t mean you have to use the Date methods of Xojos ODBC classes.
SQL distinguishes Dates from DateTimes due to one having a not having a time part and other other with. This is all fine and stays in the world of SQL.
In Xojo an SQL Date or DateTime can be stored within a Xojo Date or DateTime object. Both Xojo Date and DateTime supported Time parts. This is confusing at first but once you realise that you can use Xojo DateTime for everything the world becomes a lot easier.
The advice is to stick to DateTime everywhere in your Xojo code. Don’t try and mix Date with DateTime objects. It will only lead to trouble.
So in terms of your ODBC connection you would use the fields “DateTimeValue” properties for both SQL Date fields and SQL DateTime fields. If you do this everything should just work. Without the Windows 1601 problem.
So look at your original code. Any variable currently defined as Date change to DateTime. Any SQL Date or DateTime field change to .DateTimeValue that way you are not swapping an old style Xojo Date (with all of its limitations) with a new style DateTime.
In terms of formatting for display on screen DateTime has a Format function that allows you to choose the display output string as either Date plus time, Date only or Time only. There is also a parser to turn entered string values into DateTimes, as follows:
' DateTime to String
Var d as DateTime
Var s as String
// Date and Time
s = d.toString( Locale.Current, dateTime.formatStyles.Medium, dateTime.formatStyles.Medium )
// Just a date
s = d.toString( Locale.Current, dateTime.formatStyles.Medium, dateTime.formatStyles.none )
// Just a time
s = d.toString( Locale.Current, dateTime.formatStyles.None, dateTime.formatStyles.Medium )
// String to DateTime
d = DateTime.FromString( DateTimeValue, Locale.Current, TimeZone.Current )
Just to point out that the ODBC driver is the way to go. The Native SQL driver hasn’t been updated my Microsoft is a long time.
Hi Ian, thanks for taking your time.
I think I understand quite well how the DateTime class works.
My problem is that in the database (both SQLite and SQL Server) I can store dates before 1601, but the ODBC plugin doesn’t display them correctly (replaces the year with 1601), while with the MSSQLServer native plugin it works fine.
It also works well with SQLite, but it doesn’t work with the MBS plugin (but I’ve contacted Christian about this, and I’m waiting for his response).
To read the table records obviously I use the RowSet, but as soon as I read a record the date before 1601 in the RowSet is not correct (the year is changed to 1601).
If I can’t find alternative solutions, I would say that the way, with SQL Server, is to use the native plugin.
Thank you very much!
What driver are you using with the ODBC plugin. It may require updating. The API that the native SQL server plugin uses is no longer being updated by Microsoft, but the ODBC one is.
Why do you need the ODBC plugin for SQLite?
Hi Tim, I use the ODBC plugin with SQL Server
Hi Ian, I use the SQL Server Native Client 11.0
The ODBC driver for SQL Server is currently at version 18.2.1.1.