Query to get records which are GETDATE()-60 DAYS

Question

Query to get records which are GETDATE()-60 DAYS

balasach82

SSCrazy Eights

Points: 9653
More actions
March 9, 2012 at 7:08 am

#258379

I need to retrieve records whose DATEFIELD is less than 60 days.
select top 1 * from TABLENAME where ISDATE(DATEFIELD) = 1
and convert(datetime,DATEFIELD) <(GETDATE-60)
DATEFIELD --> varchar (10)
Since DATEFIELD is varchar, I convert it to datetime and checked whether it falls within the prior to 60 days period.
Error:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How to resolve this?

Viewing 6 posts - 1 through 5 (of 5 total)

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

SkyBVI SSCoach Points: 15128 More actions · Answer 1

balasach82 (3/9/2012)
I need to retrieve records whose DATEFIELD is less than 60 days.
select top 1 * from TABLENAME where ISDATE(DATEFIELD) = 1
and convert(datetime,DATEFIELD) <(GETDATE-60)
DATEFIELD --> varchar (10)
Since DATEFIELD is varchar, I convert it to datetime and checked whether it falls within the prior to 60 days period.
Error:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How to resolve this?

try this

select top 1 * from TABLENAME where ISDATE(DATEFIELD) = 1

and convert(datetime,DATEFIELD,101) <(GETDATE-60)

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 2

or

DECLARE @datefield VARCHAR(10)

SELECT CAST(@datefield AS DATETIME)

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 3

1. First query returns the same error

2.

DECLARE @datefield VARCHAR(10)

select @datefield = CAST(DATEFIELD AS DATETIME) from TABLENAME select @datefield

returns only one record after which it returns same error. The one record returned is not the same but different values everytime it is run.

When i increase the DATEFIELD to >10, then all the records are retrieved. If the length is the issue, then the 2nd query should not run at all, but it returns one record.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

balasach82 (3/9/2012)
I need to retrieve records whose DATEFIELD is less than 60 days.
select top 1 * from TABLENAME where ISDATE(DATEFIELD) = 1
and convert(datetime,DATEFIELD) <(GETDATE-60)
DATEFIELD --> varchar (10)
Since DATEFIELD is varchar, I convert it to datetime and checked whether it falls within the prior to 60 days period.
Error:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How to resolve this?

Put this in your where clause

where ISDATE(DATEFIELD) = 1

and DateField < convert(datetime, dateadd(d, -60, getdate()), 120)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 5

Perry,

I get the same error.

Query-1:

select top 1* from TABLENAME where

isdate(DATEFIELD) = 1

and convert(datetime,DATEFIELD) < getdate()-120

and VARCHARFIELD in ('111111','222222')

Above query works fine. But when i include another value in VARCHARFIELD as,

VARCHARFIELD in ('111111','222222','333333') .. it throws the error.

and runs fine for

VARCHARFIELD in ('111111','222222') or VARCHARFIELD ='333333'

Underlying data looks fine since I ran the above query-1 without the VARCHARFIELD filtering (and anyway isdate(DATEFIELD) is being used)