Subscribe to this thread
Home - General / All posts - SQL Server Express date format
bclement
275 post(s)
#25-Apr-22 15:59

Does Manifold V9 support the date, time, offset format?

oeaulong

521 post(s)
#25-Apr-22 16:45

if by support, you mean the datatype that manifold uses internally, then no.

Dates and time

The datetimetype represents data that contains a calendar date (day, month, year) and time (hour, minute, second, millisecond) ranging from January 3, 0001, 00:00:00.000 to December 31, 9999, 23:59:59.999

from https://manifold.net/doc/mfd9/data_types.htm

The storage is not timezone aware. So you can use .NET or SQL server to receive and manipulate the DateTimeOffset format but will have to do some manual transforms (either scripted in SQL, or Script, or through the Transforms on DateTime and Text datatypes for what I would call support) on this to place date in Mfd's DateTime format.

I may be completely off base on this and there is more mature methodologies to deal with date and time with zone offset, but this is my perspective.

bclement
275 post(s)
#25-Apr-22 17:20

Thank you so much oeaulong.

It is not lost on me that you are including a quote from the fine manual. I did search but went too specific it seems looking for information on offsets. My assumption was that Manifold would load whatever formats MS SQL server would allow and that seems to have blinded me to a simpler search. I will try a basic search in the future before posting.

And yes, by support, specifically I was looking for a reason, other than me just making a mistake, why I had date times in the DB but not in the tables in Manifold once I connected. All of the fields showed null. The queries I ran to see if some or all of the fields were null, confusingly, were not consistent in their result. But I suppose there is a reason for that since it is in a format that is not recognized (perhaps a more accurate term). Although, and I just rechecked to make sure I have not lost all my marbles, I am confused by the fact that records were returned using IS NOT NULL in the WHERE clause when in the results table, all of the few records returned were showing <NULL>. When I used IS NULL, I get no records returned which makes no sense to me whatever. It seems the format has confused Manifold as to whether or not there is data in that field.

I would like to get better at searching the manual. Do you follow a particular strategy you would like to share when searching on a topic, or are your instincts just better than mine?

oeaulong

521 post(s)
#25-Apr-22 17:45

Once the search box got added to the Release 9 Docs online, then this sort of site search was easy. I opened up 9 and made a datetime field, adding some data to it seemed reasonable. Then opened up the query builder to search for date. Lots of goodies. Following, a google search produced lots of DateTime vs DateTimeOffset sites. Some checking into these and I had the grasp of the realm of .NET & Sql server adoption of the DateTimeOffset type. This is why I hesitated as I haven't fully tried this. It would be interesting to me to find out what happens if you retrieve a SQL Server DTO field and cast it into a text type and a float64 to examine.

So, no strat, just some superficial dives into the issue.

I don't have an opinion on the NULL / NOT NULL query results. That part of your thread(s) I am observing from my seat.

good luck.

bclement
275 post(s)
#25-Apr-22 18:07

Wow, ninja instincts! Never occurred to me to look at the underlying cause with looking into .NET and the SQL specification.

Not only have you instructed me on how to get better at searching by sharing that information, but you have pointed me in a new direction by asking the questions about casting datatypes.

I am deeply in your debt. Thank you.

bclement
275 post(s)
#26-Apr-22 00:38

As an update I have found that issuing a query on the database using the CONVERT function to change the DTO (Date Time Offset) to an nvarchar works, but I would rather have the Date Time portion of the DTO data in a DT format.

In order to move on, I will probably just use Power Query in Excel to get the data.

oeaulong

521 post(s)
#26-Apr-22 00:51

for topical reading. I think the C# examples will illustrate the conversions.

https://docs.microsoft.com/en-us/dotnet/standard/datetime/converting-between-datetime-and-offset

adamw


10,447 post(s)
#17-May-22 15:21

We'll check what is going on. We completely agree that nobody should have to convert dates to strings, things should work automatically.

bclement
275 post(s)
#17-May-22 17:02

Thank you Adam. By way of both an update as well as more complete information, what seems to be happening is probably not even related to the offset. I am loading data into SQL from an ArcGIS online server that allows literally anything to be entered. For example, fat-fingered dates of 0201 or 0202 for the year have been entered. If I change the datetime fields in SQL to datetime 2 then they will go into SQL fine but then they will not come into Manifold. The simple and dare I say obvious solution is to not allow such dates, but I do not have either control nor influence in that matter. In light of that fact, I realize that there is probably neither an easy solution or perhaps a solution period. However, any help or ideas would be most appreciated.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.