
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
how to subtract date and get number of days?
hi
i have a table in which i have register date and verified date
customer_id | register_date | verified_date |
---|---|---|
1 | 01/01/2016 | 10/01/2016 |
2 | 20/01/2016 | 25/01/2106 |
in want to subtract verified_date - register_date and add a column no_of_days
thanks in advance


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Assuming they are actual dates, you can just do that.
In the front-end you will probably have to add sum to it:
sum(verified_date) - sum(register_date)
... but only do so if you are 100% sure they are a single value per customer_id
I would suggest you do this in the script by adding a field to that table like this:
verified_date - register_date as no_of_days,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Gaurav,
By just subtracting 2 dates you get the number of days:
=Date1-Date2
But of course if your have lot of rows i will recommend as Onno to create a calculated field in your loading script.
Hope this help
Maurice

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For the script here is an example using interval also:
MyData:
LOAD
verified_date,
register_date,
Interval(verified_date-register_date,'d') as Nbdays
...
If you want to force diff date even when first date less than max date use:
Fabs(Interval(verified_date-register_date,'d')) as Nbdays
Best,
Maurice

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Like everyone has mentioned above me that if you need the difference between the two dates, you can just subtract the smaller date from the larger date. If you just want to know the working days between the two dates, you can use NetWorkDays() function.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In script simply try
floor(verified_date) - floor(registered_date) as No_of_days

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Verified_date_Registere_Date
