6 Replies Latest reply on Dec 8, 2016 4:18 AM by Allen Gapara

Difference in days from Dates Ignoring year

Allen Gapara Dec 8, 2016 12:53 AM

Please help, I am stuck. I would like to find out how far out from Date of Birth do people order. I have date of birth and order date. How do I get the number of days from their Birthday do people place an order. The years are not required in the differences. I have Tableau desktop 9.3.

1. Re: Difference in days from Dates Ignoring year

Matthias Goossens Dec 8, 2016 1:15 AM (in response to Allen Gapara)

Hey Allen,

Have you tried the DATEDIFF function?
DATEDIFF('days',[Birth Date], [Order Date])

It should look something like the example above.

Cheers,
Matthias
Like Show 0 Likes(0)

Actions
2. Re: Difference in days from Dates Ignoring year

Allen Gapara Dec 8, 2016 2:02 AM (in response to Matthias Goossens)

Yes, I have tried that. It is taking year into account. For example DOB 31/05/1996 and Order Date 29/05/2014 gives me 6572. I have tried 'day' and 'dayofyear'. I expect an answer of 2 days, since it is 2 days before birthday. Thanks for your help.
Like Show 0 Likes(0)

Actions
3. Re: Difference in days from Dates Ignoring year

Sujay Paranjpe Dec 8, 2016 3:00 AM (in response to Allen Gapara)

Hi Allen,

I am wondering if this would help.

A. DATEDIFF('day',DATETRUNC('year',[Order Date]), [Order Date])
Calculates the difference in [order date] and first day of [order date]'s year in days

B. DATEDIFF('day',DATETRUNC('year',[DOB]), [DOB])
Calculates the difference in [DOB] and first day of [DOB]'s year in days

e.g. for order date 31 Jan 2016 A will be 30 and for DOB of 02 Feb 1981 B will be 32

now you can do (B - A) to calculate the 'days to birthday'

P.S. You may need to be careful about the leap year etc.

Best,
Sujay
Like Show 0 Likes(0)

Actions
4. Re: Difference in days from Dates Ignoring year

Allen Gapara Dec 8, 2016 3:28 AM (in response to Sujay Paranjpe)

Thanks Sujay,

I will need to take into account the leap year. However I am getting the following error

"[Microsoft]{SQL Server Native Client 11.0][SQL Server] The conversion of a date data type to a datatime data type resulted in an out-of-range value.

Once again , thank you for your help.
Like Show 1 Likes(1)

Actions
5. Re: Difference in days from Dates Ignoring year

Sujay Paranjpe Dec 8, 2016 3:43 AM (in response to Allen Gapara)

Hi Allen,

You are welcome. If you think my response was correct, you can mark that the question has been answered so that it goes off the open question list on the forum. If you think my response was helpful then you can click on 'This helped me' link.

Best,
Sujay
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
6. Re: Difference in days from Dates Ignoring year

Allen Gapara Dec 8, 2016 4:18 AM (in response to Sujay Paranjpe)

I think I got it now. I am using 2016 as the leap year.
First put DOB year to 2016
STR(DATEPART('day',[Customer Dob])) + " " + STR(DATENAME('month',[Customer Dob])) + " " + STR(DATEPART('year',#29/02/2016#))

Second, Put Order date to one year 2016
STR(DATEPART('day',[Order Datetime])) + " " + DATENAME('month',[Order Datetime]) + " " + STR(DATEPART('year',#29/02/2016#))

Then for DateDiff
DATEDIFF('day',DATE([DOBdateExp]),DATE([orderDateExp]))

I can then put the necessary filters on the MAX DateDiff period.
Like Show 0 Likes(0)

Actions