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

# Difference in days from Dates Ignoring year

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

Hey Allen,

Have you tried the DATEDIFF function?

DATEDIFF('days',[Birth Date], [Order Date])

It should look something like the example above.

Cheers,

Matthias

• ###### 2. Re: Difference in days from Dates Ignoring year

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.

• ###### 3. Re: Difference in days from Dates Ignoring year

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

• ###### 4. Re: Difference in days from Dates Ignoring year

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.

• ###### 5. Re: Difference in days from Dates Ignoring year

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
• ###### 6. Re: Difference in days from Dates Ignoring year

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.