-
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
-
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.
-
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
-
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.
-
5. Re: Difference in days from Dates Ignoring year
Sujay Paranjpe Dec 8, 2016 3:43 AM (in response to Allen Gapara)1 of 1 people found this helpfulHi 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
-
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.