asked on

MS SQL 2008 Query for Records with a Creation Date that uses Clarion Dates

I have the following SQL script

Use Database Name
delete
from [Database].[Table].[Name]
where c_date < 76745

Open in new window

The date is in Clarion format. I know that it is an integer date and that there is a differance of 36161 from the dates that SQL users. I do not know SQL that well so what I wanted to do is delete all records that are older than 14 days.

So i need to do something like this.

IntegerFormat((Today() - 14 Days)) + 36161

My syntax is all wrong. Just donlt know how to do it in MS SQL.

Ephraim Wangoya

delete
from [Database].[Table].[Name]
where c_date < DATEADD(DD, (36161-14), GETDATE())

Ephraim Wangoya

actually should be

delete
from [Database].[Table].[Name]
DATEADD(DD, 36161, c_date) < DATEADD(DD, -14, GETDATE())

Ephraim Wangoya

sorry missed where

delete
from [Database].[Table].[Name]
where DATEADD(DD, 36161, c_date) < DATEADD(DD, -14, GETDATE())

Ephraim Wangoya

if you want it to run faster, you can calculate the current date minus 14 first

declare @CHECKDATE datetime

set CHECKDATE = DATEADD(DD, -14, GETDATE())

delete
from [Database].[Table].[Name]
where DATEADD(DD, 36161, c_date) < CHECKDATE

Neadom Tucker

ASKER

So i tried:
delete
from [Database].[Table].[Name]
where DATEADD(DD, 36161, c_date) < DATEADD(DD, -14, GETDATE())

I replaced delete with select * to run the test but it returned no results. I should get about 4200 rows.

Let me clarify how the c_date is stored. It is stored as an integer.

So if I was looking to select all records that were less than 14 days from today. I would need my where statement to be where c_date < 76745

The serial integer for today is 40598
I need to add 36161
Then subtract 14 days

So 76754

I am trying to get the 76745 to be calculated in the where statement. Is this the right process?

Thanks again for your help!

Ephraim Wangoya

so convert your c_date to datetime then compare to todays date -14

delete
from [Database].[Table].[Name]
where CAST(c_date + 36161 AS DATETIME) < DATEADD(DD, -14, GETDATE())

Open in new window

Neadom Tucker

ASKER

Still returns no rows.

This is what I have:

Use Database
select *
from [Database].[tableobj].[table]
where CAST([c_date] + 36161 AS DATETIME) < DATEADD(DD, -14, GETDATE())

c_date is the field name.

When I run
Use Database
select *
from [Database].[tableobj].[table]
where c_name < 76754

I get 4700 records.

Thanks

Ephraim Wangoya

Your query is using c_name

select *
from [Database].[tableobj].[table]
where c_name < 76754

Ephraim Wangoya

Here is my sample test

declare @T Table (x int)

insert into @T
select 4437 --this would be today
union all
select 4437-21 --this would be today -21 days

select *
from @T
where CAST([X] + 36161 AS DATETIME) < DATEADD(DD, -14, GETDATE())

Can you show me some of the data in the table (Clarion dates only)

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Neadom Tucker

ASKER

Ok so we are making progress But I am not getting the right number of results. Your query is showing more than it should.

The Clarion Date for today is 76753
I take that and - 14 days
so my clarion date would be 76739

When I run the Query aganist the [c_date] with a clarion date less than 76739 I get 4098 rows

When I run your query and the calculation I get 4258 rows

The last c_name on your query shows the last c_date of 76746
The last c_name on my query shows the last c_date of 76738

I am not sure how that would translate into SQL.

Thanks again sooo much for your help this think is kicking my tail.

Neadom

Neadom Tucker

ASKER

Here is some of the data

Open in new window

Ephraim Wangoya

Interesting

When I calculate the date for 76753 it comes to 02/18/2011 which is exactly one week off from Today

According to my calculations, todays clarion date is 76760
SELECT DATEDIFF(day, '18001228', GETDATE())

Thats where we get the difference in number of records

How did you calculate todays date (76753)

I'm sure we will get to the bottom of this

Neadom Tucker

ASKER

I used this:
http://www.practicebetter.com/index.php?option=com_content&view=article&id=17:dateconvertor&catid=1:inhouseblog&Itemid=2#end

Ephraim Wangoya

According to that site which matches my calculations, todays date in Clarion 76760

76753 is last week on Friday - 02/18/2011

So the query is correct since it gets data up to 76738
which according to the calculations is 02/11/2011, thats exactly two weeks ago from today

Neadom Tucker

ASKER

Ahh!!! Ok I was doing something totally different on my calculation.

Thanks a million!!!!