Simple query to select records older than 2 days

Question

Simple query to select records older than 2 days

Isabelle2378

SSCertifiable

Points: 6174
More actions
February 12, 2007 at 1:03 pm

#175909

Hi,
This is probably a simple one...I just need to select a count of records from a table that is older than two days. I have the following script:
SELECT
COUNT(*) FROM FFERPSQL.MVX701.MVXJDTA.CJBCMD
WHERE
CMRGDT > (GETDATE()-2)
and I'm getting the error:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
Can someone help point me in the right direction. I don't need anything fancy, just the number of records in the table that have a date older than two days.\
Thanks!!!
Isabelle
Thanks!
Bea Isabelle

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Don't use COUNT(*). Replace the * with your datetime column. The other thing is, what datatype is CMRGDT? Is is actually datetime?

-SQLBill

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

Hi SQLBill,

The column is numeric(6,0) and the values are like this: 20070114 for Jan 14, 2007.

If I just use *, it will return all the rows. I just need a number because we are setting up a monitoring software and if the query returns 0, all is good. But if the query returns a number other than 0, send an alert.

Thanks!

Isabelle

Thanks!
Bea Isabelle

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

I think the datatype would have to be decimal(8,0) to store enough data to do this. Try this simpe example, maybe it can help you out.

create

table #t (

c1 decimal(

8,0)

)

insert

into #t values (20070114)

select

* from #t

where convert(varchar(8), c1) < dateadd(dd, -2,(getdate()))

Not pretty, but it works...

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

Anders is correct... the reason why you're getting the arithmetic overflow is because SQL Server looks at those types of numeric dates as the number of days since the 1st of January, 1900. The largest SQL is 12/31/9999 which is only a number of 2,958,463 days since 01/01/1900. You're trying to pass 20,070,114 which is a we bit larger than the max allowed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

[KH] SSCommitted Points: 1908 More actions · Answer 5

this will be more efficient. Applying functions on the column will discourage use of index

SELECT COUNT(*)

FROM FFERPSQL.MVX701.MVXJDTA.CJBCMD

WHERE CMRGDT > convert(int, convert(varchar(10), getdate() - 2, 112))

SQLBill SSC Guru Points: 51440 More actions · Answer 6

I'll back up the rest. You are comparing apples and oranges, as the saying goes. Both are fruits, but they aren't the same thing. Both CMRGDT and GETDATE() are date values, but they aren't the same thing. CMRGDT is a decimal value and GETDATE() is a DATETIME value. Also, GETDATE() includes the time. Using the 112 style will convert GETDATE() to a yyyymmdd value and make the comparison easier.

-SQLBill

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 7

The interesting thing is that it is not returning the correct amount. I used:

SELECT

COUNT(*)

FROM

FFERPSQL.MVXTST.MVXJDTA.CJBCMD

WHERE

CMRGDT > convert(int, convert(varchar(10), getdate() - 2, 112))

and my result was 4. But I checked the table and there are 205 records that have a date field of which 201 of them are older than 2 days, so the number I'm expecting is 201. This number is not including the zeros.

Here is an example. I have a table with 5 rows of which only 1 row is not older than 2 days:

CMRGDT
0
0
20070201
20070202
20070213

So I would need the query to bring back 4 rows. When I run the query suggested:

SELECT

COUNT(*)

FROM

FFERPSQL.MVX701.dbo.beatest

WHERE

cmrgdt > convert(int, convert(varchar(10), getdate() - 2, 112))

and only get 1 row back. So even if it is not including the zeros, I should still get 2 rows. Any idea about the discrepancies?

Thanks,

Isabelle

Thanks!
Bea Isabelle

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Since that query has a count(*) in it, it will never return more than one row.

SQLBill SSC Guru Points: 51440 More actions · Answer 9

Why do you expect to get 2 back? If you run just this:

SELECT convert(int, convert(varchar(10), getdate() - 2, 112))

you get: 20070211

and there is only ONE row that is greater than that date.

Your WHERE clause is basically this for today:

WHERE cmrgdt > 20070211

or WHERE the value in cmrdt is GREATER THAN 20070211. Only 20070213 meets that criteria.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 10

I think I understand the problem now. You think you are working with DATES and you aren't. Those aren't DATETIME datatypes. You are working with INTEGERS.

20070213 as an INTEGER is greater than 20070211.

20070210 as an INTEGER is less than 20070211.

You have two options:

1. work with the values as INTEGERs and accept the issue.

2. convert CMRGDT to DATETIME and compare it to GETDATE() without converting it to INT.

-SQLBill

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 11

Maybe I'm thinking about this wrong. I need the query to return the number of rows that are older than two days. So I would expect any rows that have a date of the 12th or 13th (today) to be okay and return the count of all other dates before that. So in my example, it should have returned all the dates except the 13th. As for the zeros, I'm not sure how to handle those.

Isabelle

Thanks!
Bea Isabelle

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 12

Sorry SQLBill. I didn't see your last post before I replied. I think I do need to rethink this a bit.

Thanks for all the replies and at least get me started in the right direction!

Isabelle

Thanks!
Bea Isabelle

SQLBill SSC Guru Points: 51440 More actions · Answer 13

If you go with option #2, keep the following in mind:

1. When you convert CMRGDT to DATETIME it will have the default time of 00:00:00.

SELECT CONVERT(DATETIME, drv.CMRGDT)

FROM (SELECT MAX(CMRGDT) FROM FFERPSQL.MVX701.MVXJDTA.CJBCMD) drv

2. GETDATE() always returns a time.

SELECT GETDATE()

3. You cannot convert a DATETIME value to a specific style and keep it DATETIME.

I suggest just doing:

WHERE CONVERT(DATETIME,CMRGDT) > GETDATE() - 2

It means more conversions (since it has to convert the entire CMRGDT column), but it will be more accurate than working with INTEGERS.

-SQLBill

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 14

Maybe I misread and, if so, sorry...

If CMRGDT is an integer, I don't think that WHERE CONVERT(DATETIME,CMRGDT) > GETDATE() - 2 is going to work without an extra conversion like the following...

WHERE CONVERT(DATETIME,STR(CMRGDT)) > GETDATE() - 2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)