Converting Date on-the-fly

(Peter) #1

How do I convert a Date I receive from the SQL query on the fly?

I connect to the Database and get a collection (!) of three values: StartDate, EndDate and Desctiption. All I need is to write a message like

You start on 19/06 and end on 21/06 with <<>Description>

Then output it using magic repeat, which works just fine.

However, I get the date in ISO 8601 format and instead of tidy message as wanted above I get a terrible

You start on Tue Jun 19 2018 00:00:00 GMT+0100 and end on Thu Jun 21 2018 00:00:00 GMT+0100 with <<>Description>

All I need is to change the Date, which I can do with Format Date, but it only works with the first item in the collection (i.e. StartDate). The EndDate stays weird and all further sets (I may have many which is why using magic repeat is vital) stay long and unreadable.

How do I convert every Date in a collection I receive into a DD/MM (or DD/MM/YY if it is any easier) format and output it.

I thought about adding a function into my currently working code:

You start on {{run_a_query.result__rows__0__StartDate *}} and end on {{run_a_query.result__rows__0__EndDate *}} with {{run_a_query.result__rows__0__Description *}}

like

You start on {{run_a_query.format_date.date.result__rows__0__StartDate *}} and end on {{run_a_query.format_date.date.result__rows__0__EndDate *}} with {{run_a_query.result__rows__0__Description *}}

But any corrections do not seem to work… :frowning: Any ideas here? I believe I am not the one who wants to output a collection of dates in an understandable format…

(Daniel Beckett) #2

Hi @Peter

You could use the Text service to split your outputs so that you can format each one into a readable format.

(Karen Barker) #3

Hi @Peter,

Alternatively you could look at using the code service to format the dates into the format you require.

Something like this should do the trick :slight_smile:

Screenshot-2018-6-11 Flow XO.png640×795 23 KB

You’ll then be able to reference the data in your send a message action as:

2 Likes
(Peter) #4

@DanielBeckett, thanks for the reply. I obviously thought about parsing the output, but I am worried about the magic repeat and the fact that I need to pass a collection to the parser.

I am sorry for dumb questions, but I am relatively new in this type of coding and I am trying to adjust my old skills (quite limited anyway) to this new environment. This is why I thought about more straightforward kind of .todate() option.

Can you give me a hint of the code you mean given the previous result of an SQL query which looks like

{
  "rows": [
    {
      "StartDate": "2018-06-01T00:00:00.000Z",
      "EndDate": "2018-06-18T00:00:00.000Z",
      "Description": "Adventure 1"
    },
    {
      "StartDate": "2018-06-02T00:00:00.000Z",
      "EndDate": "2018-06-28T00:00:00.000Z",
      "Description": "Adventure 2"
    }
  ],

(and the fields description thereafter)

The trouble is that there may be unknown number of entries, which is why I have to use magic repeat…

(Peter) #5

@KarenBarker, thanks a lot. I really needed a sample code like you provided. Give me some time and I will try it and see what happens. :slight_smile:

(Peter) #6

There seems to be something slightly wrong and I am sorry for being dumb, but I can’t find what. I am getting an ‘Script Error: Unexpected token ;’ message with the code you suggested. This is obviously because I make a wrong reference to the run_a_query.results field.

I thought about changing list_rows.results_count to run_a_query.results_count, but keep getting the same error. I managed to pass the whole results of the query into the resultCount by using the internal counter:

var resultCount = {{run_a_query.result__rows #}};

and now I do see the following in the Interactions results:

Code
var resultCount = [{"StartDate":"2018-06-01T00:00:00.000Z","EndDate":"2018-06-18T00:00:00.000Z","Description":"Adventure 1","Permalink":"http://...","id":1},{"StartDate":"2018-06-02T00:00:00.000Z","EndDate":"2018-06-28T00:00:00.000Z","Description":"Adventure 2","Permalink":"http://...","id":2}];
var startDate = inputs.StartDate.split("\n");
var endDate = inputs.EndDate.split("\n");
var i;
var data = [];
var date;

for (i=0; i<resultCount; i++)
  {
    var obj = new Object();
    date = new Date(startDate[i]);
    obj.startDate = date.getDate() + "/" + date.getMonth();
    date = new Date(endDate[i]);
    obj.endDate = date.getDate() + "/" + date.getMonth();
    var json.String = JSON.stringify(obj);
    data.push(obj);
  }

resolve(data);
Inputs
{
  "StartDate": "{{list_rows.result_+_gsx:startdate *}}",
  "EndDate": "{{list_rows.result_+_gsx:enddate *}}"
}

But I still have no output and an error now saying Script Error: Unexpected token . How I have got a (.) instead of (; ), which means an error is in a different place, but it doesn’t help much…

There are additional fields in the query such as id and Permalink, but they can’t affect anything if I am understanding it correctly.

I feel I am pretty close here, but…

There seem to be something with the inputs too, since I did not change list_rows there… I have manually set resultsCount = 1; to check the rest of the code and still get the Script Error: Unexpected token .

It is pretty hard to be a newbie and not to have a debugger to check the values of the resultsCount… :frowning:

(Karen Barker) #7

Hi Peter,

You’ll need to change the inputs to be the values that you’re passing in - My code is based on Google Sheet options.

So your StartDate input will need to be {{run_a_query.result__rows__0__StartDate *}}
and you End Date input will need to be {{run_a_query.result__rows__0__EndDate *}}

The resultCount value needs to be the number of rows returned by the query - do you have that information as an output from your search query?

If not you could use this instead:

var startDate = inputs.StartDate.split("\n");
var endDate = inputs.EndDate.split("\n");
var resultCount = startDate.length;
var i;
var data = [];
var date;

for(i=0; i<resultCount; i++) {
  var obj = new Object();
  date = new Date(startDate[i]);
  obj.startDate = date.getDate() + "/" + date.getMonth();
  date = new Date (endDate[i]);
  obj.endDate = date.getDate() + "/" + date.getMonth();
  data.push(obj);
}

resolve (data);
1 Like
(Peter) #8

Karen, you are a star! :slight_smile:
Thank you so much! I am sorry for making you busy with the questions like I did.

You were right, I have no idea on how many rows I have so it works the way you suggested. I have dealt with the empty output, of course.

The only weird thing is that the month slips by one backwards. I.e.

Tue May 01 2018 00:00:00 GMT+0000 (UTC) turns into 01/04.

The day is fine and the month slips. I can fix it manually, but I wonder why this happens. I thought every time I refer to i it increases, but it is pointless and besides the month would increase by one then.

1 Like
(Karen Barker) #9

Hi @Peter,

Sorry I forgot when I was writing that code that the day and month methods in javascript start referencing from 0. Therefore it’s always 1 behind.

For some reason when I tested this the easy change which should have been just adding +1 to both the day and month didn’t work correctly so I’ve had to change the code to this to compensate for the 0 index.

var startDate = inputs.StartDate.split("\n");
var endDate = inputs.EndDate.split("\n");
var resultCount = startDate.length;
var i;
var data = [];
var date;
var day;
var month;

for(i=0; i<resultCount; i++) {
  var obj = new Object();
  date = new Date(startDate[i]);
  day = date.getDate()+1;
  month = date.getMonth()+1;
  obj.startDate = day + "/" + month;
  date = new Date (endDate[i]);
  day = date.getDate()+1;
  month = date.getMonth()+1;
  obj.endDate = day + "/" + month;
  data.push(obj);
}

resolve (data);
(Peter) #10

That is basically what I finally did. Just wondered why this happened and (shame on me) did not realise that it is the Javascript notation.

Anyway, it is only about the month. The days start with 1 so your code above adds an extra day :slight_smile: Should obviously be:

var startDate = inputs.StartDate.split("\n");
var endDate = inputs.EndDate.split("\n");
var resultCount = startDate.length;
var i;
var data = [];
var date;
var day;
var month;

for(i=0; i<resultCount; i++) {
  var obj = new Object();
  date = new Date(startDate[i]);
  day = date.getDate();
  month = date.getMonth()+1;
  obj.startDate = day + "/" + month;
  date = new Date (endDate[i]);
  day = date.getDate();
  month = date.getMonth()+1;
  obj.endDate = day + "/" + month;
  data.push(obj);
}

Anyway, it is just a perfect solution. Thank you so much for your help!

1 Like