Manipulating the date format of a data stream

Ashleigh_F1sher

I'm having trouble mapping a date for my data stream as the date format in the file is in YYYY-MM-DD (Day) for example 2019-01-18 (Fri), is it possible to take out the day (Fri) so that the date is readable?

anita

Hey @Ashleigh_F1sher ,

Good day! You can definitely manipulate your data to take certain part of the csv['Date'] value. In your example if your value is 2019-01-18 (Fri), you can extract "2019-01-18" using EXTRACT function, and then getting the system to read the format by PARSEDATE function.

Eg:
var d = TRIM(EXTRACT(csv['Date'],"(",0));
PARSEDATE (d,"YYYY-MM-dd");

Let us know if this helps. Your CSM can also advise you further if you are seeing other date format

Mareets

Hi,

I'm having a similar problem. My date variable is yyyy-mm-ddThh:mm:ssz for example: 2019-01-01T02:22:000+0000. Any advise on how to format the date so that I can complete my data mapping?

Mareets

Hi @Ashleigh_F1sher, sorry for taking over your discussion! I hoped you were able to manipulate your date variable as @anita advised. 

anita

Hi @Mareets, there are a few ways to extract the date in the format you mentioned, one way is to use the LEFT() function to get the first 10 char then parse it, eg:

var d = LEFT(csv['campaign2'],10);
PARSEDATE (d,"YYYY-MM-dd");

It would be best to also raise a ticket and attach your sample file, also indicating which data stream and account you are trying this on.

All the best!

Mareets

Hi @anita

Thank you for the suggestion above but it failed to work. I believe it is due to the fact that my date variable is a date and not a character. I think I have to use the FORMATDATE operator but I'm not 100% sure what format it should be since these attempts below doesn't work:

FORMATDATE(csv['Optin_Date__c'],'yyyy-mm-dd') 
FORMATDATE(csv['Optin_Date__c'],'yyyy-mm-ddThh:mm:ssz')

Below is an example of what the date variable looks like:

Optin_Date__c

2018-11-27T06:53:10.000Z

2019-03-19T10:35:09.000Z

2018-11-27T12:09:08.000Z

2019-04-25T18:00:18.000Z

2018-11-27T12:03:26.000Z

2018-11-27T14:21:30.000Z

2018-11-27T09:09:11.000Z

In terms of the data stream I've created a generic data stream and I'm trying to map this Optin_Date__c to Day which is a date variable which the data model Day.

Any suggestions on how to map this date variable correctly?

Mareets

anita

HI @Mareets, FORMATDATE_DEFAULT(your field) can change any format to a default format , ref:
https://support.datorama.com/en/support/solutions/articles/4000139660-formulas-in-datorama

From there you can extract the portion you need.

If you still need more assistance, it would be best to send in your ticket to [email protected] and also attach your sample file. Please mention the workspace and data stream ID too, so we can look into your current setup.
Thanks.

Karthik_Naidu

How do we format date from 25-07-2021 to 25-Jul-2021 in calculated dimensions?

Austin_Arena

Jumping in here. What about when you don't know the true format of the date field as it comes from the source via an API? I am attempting to map the fields "StartDate" and "EndDate" from Google Adwords using the API connector, but it's a string, and I have no idea what the format is. When I export from the source itself, it looks like it's 'd-MMM-yy'.