Arithmetic overflow error converting expression in to data type datetime

Question

Hoping someone can help. I've looked on the internet for a solution but none seem to resolve it.

I want to query a large table and only get the results back where a column equals today's date.

Here's the query:

select [Table1].[Field1]
from [Table1]
where [Table1].[Field1] = GetDate()

The date format is as follows:

20020630

I'm a beginner in SQL so any help would be really appreciated because I am growing fond of it.

Thank you!!! :)

Im not sure becasue the table is on a linked server and i dont have full access to it, sorry. — PDB
– PDB, Commented Aug 26, 2011 at 7:56

gbn · Accepted Answer · 2011-08-26 07:56:39Z

1

To find the broken value:

select [Table1].[Field1]
from [Table1]
where ISDATE([Table1].[Field1]) = 0

GETDATE includes a time, so you need to remove this. This assumes SQL Server 2008+

select [Table1].[Field1]
from [Table1]
where [Table1].[Field1] = CAST(GetDate() AS date)

answered Aug 26, 2011 at 7:56

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

PDB Over a year ago

Its SQL Server 2008 R2. Ive just tried the GetDate solution and that says the following:

PDB Over a year ago

Msg 243, Level 16, State 1, Line 1 Type date is not a defined system type.

PDB Over a year ago

And the first solution (ISDATE) doesnt return any values.

gbn Over a year ago

@PDB: date is a valid type in SQL Server 2008+. Your linked server must be SQL Server 2005 or earlier and this code is executing there. You need to know datatype for Field1.

PDB Over a year ago

Is there any way to find tht out by using a query?

|

Collectives™ on Stack Overflow

Arithmetic overflow error converting expression in to data type datetime

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related