CODEWITHSUNDEEP
sqlsql-serversql-insertnot-exists
User812372
User812372

Reputation: 3

New table insert if Date field is before the current time

I would like to make table inserts for any record that has a Date field, which is at least 10 minutes before the current datetime. For some reason, my table does not create new insert. Below is example code:

If Not Exists (Select * from dbo.Table where RecordName = @RecordName and DateAdd(Minute, 10, GETDATE()) >=1)
Insert Into dbo.Table (RecordName, DateField, Field3, Field4)
Value (@RecordName, GETDATE(), '0','0');

FYI I'm using RecordName as a parameter. Please help!

Upvotes: 0

Views: 72

Answers (1)

Matt
Matt

Reputation: 14341

your problem is:

DateAdd(Minute, 10, GETDATE()) >=1

You are comparing a DATETIME to an INTEGER

Seeing now is 11/1/2017 8:27 AM in my time zone, DateAdd(Minute, 10, GETDATE()) would give you '11/1/2017 8:37 AM' >= 1

And 1 as a DATETIME would evaluate to 1900-01-02 00:00:00.000 SELECT CAST(1 AS DATETIME)

So you would be testing for when does not exist that is newer than 1900-01-02 00:00:00.000 which I would assume is unlikely in your dataset.

How to correct to your intent. you likely have a date/datetime field and if you compare it to

DATEADD(minute,-10,GETDATE())

If Not Exists (Select * from dbo.Table where RecordName = @RecordName and DateField >= DateAdd(Minute, -10, GETDATE()))
Insert Into dbo.Table (RecordName, DateField, Field3, Field4)
Value (@RecordName, GETDATE(), '0','0');

Upvotes: 1

Related Questions