Hello,
I have a SQL table where i have a date column and it has the following format 1yymmdd. I have converted this to datetime using following sql query.
Cast(left(right(ltrim(str(date,25,5)), LEN(ltrim(str(date,25,5)))-3),2) +'-' + left(right(ltrim(str(date,25,5)), LEN(ltrim(str(date,25,5)))-5),2) +'-' +
left(right(ltrim(str(date,25,5)), LEN(ltrim(str(date,25,5)))-1),2) as datetime)
The above query gives me date in following format YYYY-MM-DD HH:MM:SS.
But the confusion is i want to calculate the datedifference of each row with maximum date that the query returns.
For Example:
If i run the query and it returns the following 3 dates
2016-06-08 00:00:00.000
2016-06-05 00:00:00.000
2016-06-03 00:00:00.000
So in above result set the highest date is 2016-06-08 00:00:00.000 and when i calculate the difference between it and other 2 days it should return 3 and 5 days respectively,
so what i want is to calculate the difference between the max date and every other date and select only the dates which has difference of less than 3 dayys.
I am looking forward to your help as always. Thanks alot.
What I have tried:
Googled it and could not find any exact situation