How to convert date string from JSON to date format

  • 9 December 2022
  • 4 replies

Badge +1

I am retrieving JSON data and the dates come in as part of the string. I can parse the data and see it in the log.

Query JSON All Award Date - ["44907","","44562","45219"]

How do I convert the string, like "44907" to a date format so I can post the data to a date field in SharePoint?


4 replies

Userlevel 5
Badge +11

What dates are those in a friendly format?

Take a look at the “Convert a value” workflow action: Convert a value

Badge +1

They are from Excel and look like this (see table below).

I’m using the “Read Excel File” connector from the Nintex Connector Gallery.

It pulls the data in as a JSON string.  I run Nintex actions to grab these dates.  They come into Nintex as their date integers if you will in string format.

I convert the string to an integer and all is well.

As soon  as I run a convert to go from integer to date I get the following error: The specified value is unknown. [Error Code: TypeCasting.UnknownCasting]


Userlevel 5
Badge +13

Hi @davehtx 

You can use the Add Time to Date action to convert those number into actual Date.
Add the number to the date “01-Jan-1900”.  

You posted the following
→ Query JSON All Award Date - ["44907","","44562","45219"]. Using 44907

Add 44907 days to the date “01-Jan-1900”. Excel uses the value mean 44907 days after “01-Jan-1900”.

Configure the Add Time to Date action as below

Below is the Log to History result. 14-Dec-2022

FYI, our system date follows UK style - dd-mm-yyyy

Hope that helps.

Userlevel 5
Badge +11

Piggybacking off what @Garrett said, you should be able to use the “Add time to date” action in Nintex Automation Cloud to get the same result.