Search code examples
sql-serverms-accesssql-timestamp

Compare a timestamp in WHERE clause of Access/SQL query

I'm working in an Access front end linked to an SQL Server back end.

In one of our tables, the data type on the SQL Server is timestamp. I want to write a WHERE statement that limits the data returned to records created in the last 30 seconds. I tried the following

WHERE myTime > DATEADD(s, -30, SYSDATETIME())

But get the following error:

Operand type clash: datetime2 is incompatible with timestamp

I've been researching casting and converting, but exactly where or how I would do that isn't clear. Any insight? And is using SYSDATETIME() the right thing to do?

Solution

  • You should use a datetime type for your myTime column. I think you have misunderstood the purpose of the timestamp datatype.

    From the referenced article:

    The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.