How to get reginal date and time correct with VBA (1 Viewer)

[nector]

First of all, the code attached is not mine I got off the internet and it's working ok except the regional date is incorrect its failing to pick Pretoria or Harare time Zone. Is it possible someone can assist to correct the VBA code attached.

Below is my final working of my data

]  Ì{
   "PosVendor": "Nector Prime Accounting Solutions",
   "PosSoftVersion": "2.0.0.1",
   "PosModel": "Cap-2017",
   "PosSerialNumber": "20230117220000",
   "IssueTime": "20230117220000",
   "TransactionType": 0,
   "PaymentMode": 3,
   "SaleType": 1,
   "LocalPurchaseOrder": null,
   "Cashier": "Admin Manager",
   "BuyerTPIN": null,
   "BuyerName": "MWIINDE",
   "BuyerTaxAccountName": "MWIINDE",
   "BuyerAddress": null,
   "BuyerTel": null,
   "OriginalInvoiceCode": null,
   "OriginalInvoiceNumber": null,
   "Memo": null,
   "Currency-Type": "ZMW",
   "Conversion-Rate": 1,
   "Items": [
      {
         "ItemId": 1,
         "Description": "RICE 50 KGS",
         "BarCode": "871253",
         "Quantity": 10,
         "UnitPrice": 150,
         "Discount": 0,
         "TaxLabels": [
            "A",
            "E"
         ],
         "TotalAmount": 1500,
         "IsTaxInclusive": true,
         "RRP": 0
      }
   ]
}Þ^

 

[ebs17]

You mean that?

"IssueTime": "20230117220000"

String "20230117220000" to Date:

? Format("20230117220000", "@@@@-@@-@@ @@:@@:@@")
2023-01-17 22:00:00
? CDate(Format("20230117220000", "@@@@-@@-@@ @@:@@:@@"))
17.01.2023 22:00:00
? DateAdd("h", 8, CDate(Format("20230117220000", "@@@@-@@-@@ @@:@@:@@")))
18.01.2023 06:00:00
? CDate(Format("20230117220000", "@@@@-@@-@@ @@:@@:@@")) + #07:48:10#
18.01.2023 05:48:10

 

[nector]

Yes, exactly that what I mean!

Now that I doubted my earlier answer which gave me exactly what I was looking for. See below:

Date Add ("n", 120, Now ())

I will finally adopt it unless others have reservation to the contrary.

 

[nector]

You mean that?

String "20230117220000" to Date:

? Format("20230117220000", "@@@@-@@-@@ @@:@@:@@")
2023-01-17 22:00:00
? CDate(Format("20230117220000", "@@@@-@@-@@ @@:@@:@@"))
17.01.2023 22:00:00
? DateAdd("h", 8, CDate(Format("20230117220000", "@@@@-@@-@@ @@:@@:@@")))
18.01.2023 06:00:00
? CDate(Format("20230117220000", "@@@@-@@-@@ @@:@@:@@")) + #07:48:10#
18.01.2023 05:48:10

Many thanks ebs 17

 

[sonic8]

Date Add ("n", 120, Now ())

I will finally adopt it unless others have reservation to the contrary.

The general approach is a good solution to the problem.
However, if you cannot be sure of the time zone of the user running the code, you might want to use the current Windows time zone offset instead of a hardcoded value. I wrote a text to show how to get Windows time zone information by using the Windows API.

 

[nector]

Many thanks sonic8, frankly you have a solution, but the issue here is that it requires a reference which is not stated, kindly see if you can as well provide the reference.

The other thing though small question is how to call it for the current date or transaction date.

Example Transaction date = ShipDate

Calling it can we call like below:

GetTimeZoneInformation(ShipDate) or Json format like below

transaction.Add "IssueTime", GetTimeZoneInformation(DLookup("ShipDate", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices))

 

[sonic8]

Many thanks sonic8, frankly you have a solution, but the issue here is that it requires a reference which is not stated, kindly see if you can as well provide the reference.

How did you come to that conclusion?
My solution does not need a reference, you just download the module, import it into your application, and then you got everything you need.

The other thing though small question is how to call it for the current date or transaction date.

There seems to be a misunderstanding.
My example code shows how to retrieve the time zone information from your local Windows installation. It provides the information (UTC offset) you need to convert the UTC date from your JSON to the local time of the user, without knowing the time zone of the user during development. It does not do any conversion itself. That's what you are supposed to do, as it with your DateAdd code above.

 

[nector]

Ok true I missed something here but I have managed to compile it I missed the bas module, now the only thing I'm asking is how to call it?
For example, my shipedate = #01/023/2023#

Can I call your code like below?

DisplayTimezoneInformation(shipedate)

To display the local computer time wherever the computer can be on planet earth?

I do highly appreciate your help on this one

 

[sonic8]

The DisplayTimezoneInformation is just sample code to illustrate the meaning of the information that is returned by the GetTimeZoneInformation API. You don't use DisplayTimezoneInformation but write your own code to retrieve the time zone info and use the Bias value for your conversion from UTC to local time. If the date falls into the Daylight Savings date range, you also need to add DaylightBias on top, to convert to the local daylight time.

I now see that this is something I should add to the my text linked above, but it don't know yet when I will find the time to do that.

 

[nector]

Ok thank so much I will do that

 

[Nixversteher]

For example, my shipedate = #01/023/2023#

if this is now a Utc-date (more exactly 01/23/2023 00:00:00) then only the function "ParseUtc" in your JsonCovertor.txt from your input post should be called.