Json Parse plain text to date

Json Parse plain text to date

  

Hello,


I am trying to make date records from a Json Parse File.

The File is coming as a JSON Stringt form from a  other source.


This file has more then 1 attribute and many records. Every Attribute is going well but the plain text to date is giving a error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.


I have tried many things:


TextToDate(JSON_parse.Data.Result.Current.Day)


Year

TextToInteger(Substr(JSON_parse.Data.Result.Current.Day,6,4))


Month

If(Index(Substr(JSON_parse.Data.Result.Current.Day,3,2),"0") = 0 ,
        
            TextToInteger(Substr(JSON_parse.Data.Result.Current.Day,4,1)),

            TextToInteger(Substr(JSON_parse.Data.Result.Current.Day,3,2))
)


Day

If(Index(Substr(JSON_parse.Data.Result.Current.Day,0,2),"0") = 0 ,
        
            TextToInteger(Substr(JSON_parse.Data.Result.Current.Dayb,1,1)),

            TextToInteger(Substr(JSON_parse.Data.Result.Current.Day,0,2))
)


So as you see the plain text that is coming from the plain text has the format

 (DD-MM-YYYY) (22-12-2017)


I hope someone has the answer,


Thanks a lot,


Erwin Edel


Hi Erwin,

Do I understand correctly that the date is actually "(22-12-2017)" including the parentheses? Can you show the actual input JSON?

{      "day": "07-07-1992",      "city": "######",      "Function": "#####",      "last_name": "Edel",      "title": "######",      "id": "############",      "phone": "##############",      "role": "##############",      "country": "NL",      "hid": "#####",      "mobile_phone": "############",      "Manager": "########",      "location": "#########",      "first_name": "Erwin",      "email": "erwin.edel@#######.nl"    }

Well, that's not the de facto date format typically used, so you can't parse that into a date while parsing the JSON. So you need the "day" Attribute to be of type Text instead of Date in order to be able to parse the JSON.

Afterwards, you can parse that date yourself, or if you have dd-MM-yyyy configured as your date format in Service Center, use TextToDate().

Our Service Center is configured as DD-MM-YYYY.

I have tried the TextToDate() function but then he will still give the same error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.



Hi Erwin,

At what point do you get the error? "SqlDateTime" makes me expect you're trying to insert it in the database? Have you checked the value after conversion?

I have tried to show it on a simple webpage and then it will give no Errors.

If i try to insert it in to the database it will give the error.

I have tried some function, sometimes it gave a NULLDate() and sometimes the error

Hi Erwin,

Have you debugged it? What is the result of the TextToDate() in the debugger?

Hi Kilian,


I have treid it on a simple Webpage and de result whas a good Date.

No errors, even when the record (DATE) was empty

All records where on locale Date (No Database)

Hi Erwin,

I'm not sure I can help you any further - if the output is a good date, putting it in the database shouldn't give an error. Can you attach an eSpace that exhibits this problem?