Android Question [solved] Date problem when remote query SQL Server

makis_best · Jun 26, 2018

Hi

I use this tutorial trying to retrieve data remote from my database.
https://www.b4x.com/android/forum/threads/connect-android-to-ms-sql-server-tutorial.13166/
Everything is working fine and I can retrieve easy everything I want, but there is a problem when I use dates.....

I am using a simple query with a date field.
The query is SELECT RegistrationDate AS Date1 FROM Note1
So I test it in my browser like that
http://192.168.1.1/CONN.ASPX?QUERY=SELECT RegistrationDate AS Date1 FROM Note1
I save the result in a sqllite database

But I get a result like that....

[{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"},{"Date1":"\/Date(1498856400000)\/"}

Why? what I do wrong?
Is there any way I can solve that problem or the date is correct and I don't know what to do with the result?

keirS · Jun 26, 2018

That's a date value as ticks. So just use DateTime.Date() to get the string representation.

makis_best · Jun 26, 2018

I understand.... but if i want to format it in query?
That means.... that if I want to store the date to sqlite it must
stored to a field with type long...
Correct?

keirS · Jun 26, 2018

Look at the CAST, CONVERT and FORMAT functions in T-SQL.

makis_best · Jun 26, 2018

I don't think that Date(1498856400000) is a tick value cuz every try I do I get crazy results.
I think that something is wrong with the base value.

I don't think it is tick value.

When I use this page http://tickstodatetime.com/ and I test 1498856400000 as value
doesn't return a date.

keirS · Jun 26, 2018

B4X:

Log(DateTime.Date(1498856400000))

gives me: 06/30/2017

makis_best · Jun 26, 2018

keirS said:
B4X:

Log(DateTime.Date(1498856400000))

gives me: 06/30/2017

The result you get in Log is not correct....
If I execute the query with order by RegistrationDate

The dates in query start from 07/01/2017

keirS · Jun 26, 2018

What date type are you using in SQL server?

OliverA · Jun 26, 2018

makis_best said:
The result you get in Log is not correct....
If I execute the query with order by RegistrationDate

The dates in query start from 07/01/2017

The difference could be time zone related. Have you tried the code to see what you get back?

makis_best · Jun 26, 2018

OliverA said:
The difference could be time zone related. Have you tried the code to see what you get back?

Yes i did.. I get 06/30/2017

OliverA · Jun 26, 2018

makis_best said:
I get 06/30/2017

And that is what it is.
Source: https://stackoverflow.com/questions/206384/how-do-i-format-a-microsoft-json-date
If you want something else returned, another format, then you need to change your ASPX page code. Note one of the link's comments:

The /Date(...)/ format is specific to Microsoft's built-in JSON Date format - it's not part of any standard, and JSON, coming from Javascript, has a standard: The ISO format Javascript specifies: stackoverflow.com/a/15952652/176877 So, this question is specific to Microsoft's JSON Date format.

So anyone with some ASPX chops may be able to help you out. But the result (the 1498856400000) comes directly from your database.

makis_best · Jun 27, 2018

OliverA said:
And that is what it is.
Source: https://stackoverflow.com/questions/206384/how-do-i-format-a-microsoft-json-date
If you want something else returned, another format, then you need to change your ASPX page code. Note one of the link's comments:

So anyone with some ASPX chops may be able to help you out. But the result (the 1498856400000) comes directly from your database.

I agree but my values in the database is from 07/01/2017 and after not 06/30/2017

OliverA · Jun 27, 2018

The number (1498856400000) comes from some database that your are accessing via your ASPX page. Using this site (https://www.epochconverter.com/), 1498856400000 is

Assuming that this timestamp is in milliseconds:
GMT: Friday, June 30, 2017 9:00:00 PM

The date is the same for me, but the time is different (since I'm in a different timezone). So the date could be displayed differently if the viewer that you are using to view the database is in a different timezone or is configured for a different timezone.

BTW: http://tickstodatetime.com/ is based on

A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond, or 10 million ticks in a second.

The value of this property represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001 (0:00:00 UTC on January 1, 0001, in the Gregorian calendar), which represents DateTime.MinValue. It does not include the number of ticks that are attributable to leap seconds.

Source: https://msdn.microsoft.com/en-us/library/system.datetime.ticks(v=vs.110).aspx

B4A DateTime is based on

The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'.

Source: https://www.epochconverter.com/

keirS · Jun 27, 2018

OliverA said:
The number (1498856400000) comes from some database that your are accessing via your ASPX page. Using this site (https://www.epochconverter.com/), 1498856400000 is

The date is the same for me, but the time is different (since I'm in a different timezone). So the date could be displayed differently if the viewer that you are using to view the database is in a different timezone or is configured for a different timezone.

Which is why I asked what the column type was for the date. SQL server supports storing dates with and without timezone offsets.

makis_best · Jun 28, 2018

In SQL Server field type is datetime(3)

keirS · Jun 28, 2018

So what timezone are you in? Because the JSON serialization in .net will take a DATETIME(3) value as being at UTC 0 (no timezone offset). When it's serialized it serializes it to the local timezone of the browser.

makis_best · Jun 29, 2018

My timezone is Athens (+3)

Until now I have try this options:
SELECT strftime('%Y-%m-%d', datetime((SUBSTR(RegistrationDate, 7, 13))/1000)/10000000 - 62135596800, 'unixepoch') AS Test,
SUBSTR(RegistrationDate, 7, 13) AS Test2,
datetime((SUBSTR(RegistrationDate, 7, 13))/10000000 - 62135596800, 'unixepoch') AS test3,
strftime('%Y-%m', datetime((SUBSTR(RegistrationDate, 7, 13))/10000000 - 62135596800, 'unixepoch')) AS test4,
datetime((SUBSTR(RegistrationDate, 7, 13))/10000000 - 62135596800, 'unixepoch') AS Test5,
RegistrationDate AS 'DAte' FROM ClientTable WHERE GID = '87e40e24-572b-4660-bbd6-747e6e83da79'

And the result I get Is that... all wrong...

victormedranop · Jul 2, 2018

I use this to set my timezone .

B4X:

DateTime.SetTimeZone(here your time zone mines -4)

when I am calculating dates.

Victor

makis_best · Jul 2, 2018

I don't want to do the conversion from tick to date in vb.
I want to do the conversion on sqllite script.
There is no way?

nibbo · Jul 2, 2018

makis_best said:
I want to do the conversion on sqllite script.
There is no way?

Not sure about SqlLite but in SQL it is:

B4X:

DateAdd(S, Convert(int,left(YourDateColumnName,10)), '19700101')

Android Question [solved] Date problem when remote query SQL Server

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency