Software

Ask something
Discussion Locked

Time Calc field

0 helpful
2 replies

by athiel

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In one of the tables in SQL 2000 I need to calculate the diff in # of days between several actions:

I use the following formula in the design screen of the table:

(datediff (dd, colname, getdate( ))

but I get an error.

Does this have to do with missing an = sign or not using caps? or does anyone see where the problem is.

I cannot insert the exact date as the column on which the calc needs to take place contains over 200 000 records.

Any help appreciated

Albert

2 replies

by -oj

this little example might help:

create table #tmp (sdate datetime, edate datetime)
insert #tmp select '20020101','20020301'
union all select '20020102','20020103'

select datediff(dd,sdate,edate) as [diff]
from #tmp

by mrspost

Your date field in the database might not be registering as an actual date.

I see an extra parenthese at the beginning of the line but I'm thinking that is because you copied it from code.

First thing I would do is display the date in the table by going to the Query Analyzer and doing something like this:

select top 10 [column] from [table]

Then, display your system date. You never know.

print getdate()

You may need to use the CONVERT command to make it work right:

datediff(dd,convert(varchar(12)[column],101),getdate())

This discussion is closed to new replies.