Problem with SQL Script with Clarion Date format

I need to run a sql script to update records in a table that were created
that day. I have a huge database and it would take a while to run a script
on all.
Here's what i have so far: (this is a select stmt version not update stmt
i'm using for testing)
SELECT tm5user.matter.c_date as CreateDate,
((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
FROM tm5user.matter
INNER JOIN tm5user.billopt ON tm5user.matter.sysid = tm5user.billopt.owner_id
WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
I got the script to work but it doesn't calculate the date correctly. The
date field "c_date" is based on a clarion base date of 12/28/1800.
I have my select statement return the "c_date" field, the calculated date
field, and full table columns for testing. The CreateDate and CalcDate
should be equal and they are not.
Does anyone have any ideas ... I tried CastDate that also did not work.
Any help or tips would be great ..... thanks.
rob bartley
msce> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate
The above expression (1800-12-28) is doing integer arithmetic. I suggest you use the DATEDIFF or
DATEADD function (depending on what you want to achieve). Also, I suggest you format the date in
language neutral format ('yyyymmdd') so it doesn't break in a nationalized environment.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <temp@.dstek.com> wrote in message news:us1cOsGtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> I need to run a sql script to update records in a table that were created
> that day. I have a huge database and it would take a while to run a script
> on all.
> Here's what i have so far: (this is a select stmt version not update stmt
> i'm using for testing)
> SELECT tm5user.matter.c_date as CreateDate,
> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
> FROM tm5user.matter
> INNER JOIN tm5user.billopt ON tm5user.matter.sysid => tm5user.billopt.owner_id
> WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
>
> I got the script to work but it doesn't calculate the date correctly. The
> date field "c_date" is based on a clarion base date of 12/28/1800.
> I have my select statement return the "c_date" field, the calculated date
> field, and full table columns for testing. The CreateDate and CalcDate
> should be equal and they are not.
> Does anyone have any ideas ... I tried CastDate that also did not work.
> Any help or tips would be great ..... thanks.
> rob bartley
> msce
>