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

[makis_best]

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]

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

 

[makis_best]

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]

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

 

[makis_best]

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]

Log(DateTime.Date(1498856400000))

gives me: 06/30/2017

 

[makis_best]

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]

What date type are you using in SQL server?

 

[OliverA]

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]

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]

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]

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]

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]

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]

In SQL Server field type is datetime(3)

 

[keirS]

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]

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]

I use this to set my timezone .

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

when I am calculating dates.

Victor

 

[makis_best]

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]

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

Not sure about SqlLite but in SQL it is:

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