Search code examples
sqldbeaver

Calculate the difference in dates with SQL

Im trying to use Mockaroo to generate random data for my database. Im trying to get two random dates and calculate the difference between the two as a third column. Does anyone have knowledge on how to do this. I tried to do a formula but it throws an error.

enter image description here

Id like DurationOfLease to be a result of EndDate - StartDate

Here is the error Syntax error in formula DATEDIFF(year, StartDate, EndDate) AS DateDiff;

Solution

  • OK, So I looked a bit more into it.

    First of all, the correct name of the function in Mockaroo is DIFF_DATE, not DIFFDATE.

    Secondly, notice how your datetime fields actually show a date range each? Mockaroo Datetime fields generate data between that range, it seems like it considers them two separate columns.

    So the syntax is failing because Mockaroo is interpreting your usage of both startDate and endDate to be a total of 4 dates. If you use only date_diff('years', startDate, startDate); it no longer gives a syntax error.

    However, I'm testing the data and it doesn't really seem to return the correct difference either, but maybe it's different with your data. Try it out with that formula instead.