Unable to assign number to date type field

[Zerkin]

Hi all,

I have a json file that I import into excel.

an example of one of the dates in the json file is 200834905 (this is the value for the user in the example below).

I have been pulling this date and data from other sheets into another sheet using ADO an example of a statement is

SELECT [Result], [Date] FROM [Results$A1:C1012] WHERE [PlayerName] = ' orchidIove? '

the date returned for this user via ADO is "22/05/2022 5:25:21 PM" and 44703.7259375 as a double

So I have no problems with the above but I want to import this information via the json file into an access database.

Using a very similar method (VBA ADO connection in an access database) I attempt to assign a value such as 200834905 from the json file to an access database such as RS("FleetJoinDate").Value = Value(7)

This breaks the code with error "out of present range" which makes sense as the json value is too large to fit in the date data type that the FleetJoinDate field is set to.

I cant CDate the json value and have run out of ideas as to where to go next.

Hopefully this is enough information to convey what is going on.

Any help would be appreciated. Thanks

[Gasman]

So how is that number generated meant to mean to be able to be equal to what you posted?
How does Excel know how to convert it?

I would have thought you would need a UDF to convert to a decent date format for Access?
There does not appear to any defined format for a date in JSON? :(

https://stackoverflow.com/questions/10286204/what-is-the-right-json-date-format

[Zerkin]

I haven't had time to resolve this yet but I have since found out that it is coming from a unix date format. Now that I know that I should be able to apply a formula to it to represent a access date

I'll post the answer when I get to it :)

[Zerkin]

Still having issues with this. Using 200834905 as an example which should equal "22/05/2022 5:25:21 PM"

Given its a unix date which is seconds after 1970 the following should work;

RS("FleetJoinDate").Value = DateAdd("s", Value(7), "1/1/1970 00:00:00")

It comes out as "13/05/1976 11:28:25 AM" though.

[Zerkin]

I'm just going to change the field to long until I can figure it out.

[Gasman]

I'm just going to change the field to long until I can figure it out.

This site does not give anywhere near that?
https://www.epochconverter.com/

[Zerkin]

No gasman. Tried to paste but the site wont let me. You can enter the data and see the result. it's not valid

[Gasman]

Well I would not say not valid, just not what you think it is?
If you look at that site, the number for today is far greater?, so you need to find out what that number is meant to represent. It does not appear to be seconds?, as if you use that in DateAdd() and 01/01/1970, it comes nowhere near your date?
So how does excel get it correct?

[arnelgp]

there is also VBA converter here:
Excel VBA: Convert a date string to a Unix timestamp - Stack Overflow (https://stackoverflow.com/questions/12325948/excel-vba-convert-a-date-string-to-a-unix-timestamp)

also here:
vba date to unix timestamp Code Example (codegrepper.com) (https://www.codegrepper.com/code-examples/vb/vba+date+to+unix+timestamp)

[Gasman]

there is also VBA converter here:
Excel VBA: Convert a date string to a Unix timestamp - Stack Overflow (https://stackoverflow.com/questions/12325948/excel-vba-convert-a-date-string-to-a-unix-timestamp)

also here:
vba date to unix timestamp Code Example (codegrepper.com) (https://www.codegrepper.com/code-examples/vb/vba+date+to+unix+timestamp)

That appears to give the same as DateAdd and "s"

? exceldatetime(200834905 ,False)
13/05/1976 11:28:25

? dateadd("s",200834905,#01/01/1970#)
13/05/1976 11:28:25

[Zerkin]

After looking into this further I was mistaken in thinking that the date was interpreted by one ADO connection and not another. I can't get anything to convert the supplied number as a valid date.

So I'm going to try and track down the people who are exporting the data and see what's going on.

I'll post here when I make any progress. Thanks for the help so far.

[Zerkin]

Turns out the date stamps are based on a custom start date of "2016-01-06 00:00:00".

[Gasman]

Turns out the date stamps are based on a custom start date of "2016-01-06 00:00:00".
Makes a little more sense :-)

However if seconds, still not your value?
? dateadd("s",200834905,#01/06/2016#)
18/05/2022 11:28:25

You said it was meant to be "22/05/2022 5:25:21 PM"