Adding random days between two dates, without time

#1

Hi,

I’m trying to add a few random days between two date fields.

I have start and end as two fields in yyyy-mm-dd format.

I have added the following formula to the end field.

start + days(random(1, 7))

This does produce the correct result but includes the time & UTC as well.

Example.

'2015-12-11', 2015-12-16 14:35:37 UTC'

Whereas I am hoping for

'2015-12-11', 2015-12-16'

Is this expected, do I have to specify date %Y%m%d somewhere to trim it?

Thanks in advance.

#2

That’s weird, it’s supposed to preserve the date format automatically. Here’s a simple example that works as desired: https://mockaroo.com/601afa50

Would you mind pasting the “share link” at the bottom of the schema so I can take a look?

#3

I am seeing the same issue as the original reporter. I am using XML format and referring to the original field using field(xxxx.xxxx) + days(90)

#4

Can you save your schema and post the share link at the bottom?

#5

Note that I un-did the troublesome field as I needed to move on. But you could re-create by adding a field that implements the formula I pasted in the prior message.

#6

This happens to me all the time. If I set a start date in format yyyy-mm-dd, and then for the end date use a formula such as startDate + days(5), the result always includes time. Luckily, upon insert, mySQL truncates the values to just the date.

Here’s an example:

If you look at the schema, I set the date for effectiveOn and format it yyyy-mm-dd, then calculate confirmedOn by subtracting 10 days. If you hit preview, you can see the result, which always includes the time in the calculated field.

#7

Oddly enough, I’m having the same issue.

I have a created date set as a SQL Datetime. If a condition is met, status = ‘Closed’ I want to add a random number of days to determine the closed date. Closed date is also a SQL Datetime. However, for closed date it displays as UTC time. What gives?

Here’s my schema:

#8

Hi,

The issue appears to be caused if you specify a type of date and use the fx option to specify the formula, if you use the type formula to specify the date calculation then it returns only the date part.

Hope this helps!

#9

I’m having the same issue.

I start with a DD/MM/YYYY and when i use days() it is outputting it as YYYY/MM/DD plus a TIMESTAMP and sometimes a timezone… like UTC.

#10

I use a formula to set the format
this.to_date.strftime("%d/%m/%Y")

3 Likes