How do I parse out the data of birth out of a json file where by the format isn consistent e.g. "1989-03-21T01:11:13+00:00" and "15\/09\/1978", and make it a consistent format before upload

Hey there,

OK - Dates can be a little tricky to handle, but Laravel does a fine job of dealing with them ... most of the time.

In your example data, this format :

1989-03-21T01:11:13+00:00

Is actually exactly what you need. It's the ISO Date Format, so is generally the preferred way of storing dates. Laravel will drop that into the database field no problem at all. Add a $dates property to your User model and you'll have even more control over the date:

protected $dates = [
    'date_of_birth',
];

Select allOpen in new window

By doing this, Laravel will automatically convert the Dates from your Database into instance of Carbon dates, whick is really powerful when dealing with dates.

The problem you'll have is when you have dates like this:

15/09/1978

That's not a standard - from a logic point of view, it just doesn't make sense - it's purely for humans. Now that date above is clearly meant to be 15 September 1978. But what if the date were 04/09/1978 - In the UK, we'd see that as 4th September, but in the US, they'd see it as 9th April - and there in lies your problem.

How you deal with this will depend in part on what you know about the data that's coming in from your JSON file. If you absolutely know the format, then you can tell Laravel exactly what format to parse the date from:

$contact->date_of_birth = Carbon::createFromFormat("d/m/Y", $contactDetail['date_of_birth']);

Select allOpen in new window

If you don't know the format, or it could be mixed, then you're going to have to add in some logic - the really big problem you'll face is if you get US and UK dates mixed together - no real way of sorting that mess out.

And that's why we always use ISO dates :)

Thank you for your reply

I tried it but the data inside the json file I try to upload
is very inconsistent when it comes to date of birth

the codeI I used also trying to filter out what couldnt be easly decoded

$contact->date_of_birth = (!$contactDetail['date_of_birth'] === null) ? Carbon::createFromFormat("d/m/Y", $contactDetail['date_of_birth']) : new DateTime('01/01/2000');

Select allOpen in new window

this is the result: every thing is set on the same date as default :p


This is what I tried just now:

$contact->date_of_birth = (!is_null($contactDetail['date_of_birth'])) ? substr(preg_replace("([^0-9/])", "", $contactDetail['date_of_birth']), 0, 8) : new DateTime('2000-01-01');

Select allOpen in new window

the results

I tried using carbon,
but I got an error because it the data wasnt consistent and wasnt
inserted Eloquently :p, so that made it difficult to read the date

Your logic is going to fail when you have mixed format dates in there, which is why you need to add in some additional logic. The only way you'll be able to get accurate results is if you have a reliable way to identify the format of the incoming date and parse it accordingly.

I'd need to see your JSON file to be able to give you more detailed guideance

Regarding, "15\/09\/1978", it is clear it is "September 15, 1978".  Thus, that format is "dd/mm/yyyy".

However, if you had shown "07/09/1978", that could be interpreted as either "July 9, 1978" (mm/dd/yyyy) OR "September 7, 1978" (dd/mm/yyyy).  So, my question to you is, for the dates that do NOT use the ISO date format (1989-03-21T01:11:13+00:00), are you sure all those dates actually mean "dd/mm/yyyy"?  If so, I suggest you use the strtotime() format:

Note:
Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed. If, however, the year is given in a two digit format and the separator is a dash (-), the date string is parsed as y-m-d.

https://www.php.net/manual/en/function.strtotime.php

with the caveat that you will need to change the "/" to "-" as per the note above.

So, if you are sure that what you have is dd/mm/yyyy, try:

$contact->date_of_birth = 
date('Y-m-d',strtotime( str_replace('/','-',$contactDetail['date_of_birth'])) );

Select allOpen in new window

On another note, on your first block of code you are setting the email to a date_of_birth:

$contact->email = $contactDetail['date_of_birth'];

Hey there,

Right. I've had a look at your JSON (although I haven't gone through it completely - 10,000 records!), and it seems that you have several formats, include null values:

15\/09\/1978
2003-10-31T13:31:56+00:00
1955-12-05 00:00:00
null

As long as you set up your Model correctly (at the 'date_of_birth' to the $dates property and set your DB column to nullable), then Laravel will happily deal with 3 of these formats withou you having to do anything. The one it struggles with is 15/09/1978 (after you've decoded the JSON, the escape slashes get straipped out automatically). Because of that you only need to deal with that specific format. The quickest / easiest way is just to check the length of the value - if it's 10, then deal with it, otherwise let Laravel deal with it:

$contact = new Contact;
$contact->name = $contactDetail->name;
...
$contact->date_of_birth =
    (strlen($contactDetail->date_of_birth) == 10)
    ? Carbon::createFromFormat('d/m/Y', $contactDetail->date_of_birth)
    : $contactDetail->date_of_birth;

$contact->save();

Select allOpen in new window

Thank you very much Chris

i tried it but I unfortunatley got this error:

Thank Chris is worked

Good job :)