find # of days passed

Hello,

I need to take one of my date column and compare it with the current date and see how many day difference? How can I do this?

Thanks,

SELECT
DATEDIFF( day, myDateColumn, GetDate()
FROM
myTable
DATEDIFF is a built in sql function, the first parameters tells what "value" to return,
day = number of days,
the second two parameters tell it what dates to make calculations against.
bill

|||in some function...
public TimeSpan DateDifference(DateTime date1, DateTime date2)
{
return new TimeSpan(date2.Ticks - date1.Ticks);
}
..i didn't test for negative, so if you just want the difference (where could be that 1 < 2 or 2 < 1) then maybe something like
public TimeSpan DateDifference(DateTime date1, DateTime date2)
{
return new TimeSpan(Math.Abs(date2.Ticks - date1.Ticks));
}
|||

You need the DATEDIFF function, if you write VB you are in luck but it is not available in C# so you can use the T-SQL version. Try the links below to get started. Hope this helps.

http://www.stanford.edu/~bsuter/sql-datecomputations.html
http://www.15seconds.com/issue/041013.htm

|||I can bring it as a new column, however main objective is to some amounts
and give three totals such as "Current Amount", "30-60 days amount", "> 60
days amount". How can I do this in Reporting Services?
Thanks,
|||This will pull each of the aging into its own column. myDateColumn is the date you want to check. When the DateDiff is in given amount, the amount will be summed. When it falls outside the (ie < 30 ) range, it will sum 0.
Select
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) < 30 Amount else 0 End ) as Curent,
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) between 30 and 60 Amount else 0 End ) as Thirty,
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) > 60 Amount else 0 End ) as Over
...
'rest of your query.
bill|||

Thank you very much for your help. Is it possible to this in RS, My query is already so complex, I am trying to see if I can do it in RS.

|||Are you looking for a "check box" that will do this for you? I do not believe one exists.
bill|||

:-)))). I am sure there is a way to do it in RS, there are lots of ability to write code there, I did not get training yet, that is why I am asking. Have you ever written a function in Code window? Is there a good reference that can explain it to me.