CODEWITHSUNDEEP
sqlsql-serverssms
Vishal D Makwana
Vishal D Makwana

Reputation: 13

Date compare and difference

I have "Employee" table with employees' "StartDate".

I want to get a list of all employees who are reaching their work anniversary next month.

So far I have reached up to this point:

SELECT *
FROM Employee
WHERE DATEDIFF(DAY, DAY(StartDate), DAY(GETDATE())) = 30

...but this doesn't seem to be working.

Upvotes: 0

Views: 51

Answers (2)

Squirrel
Squirrel

Reputation: 24793

I want to get a list of all employees who are reaching their work anniversary next month.

first you find the next month date

DATEADD(MONTH, 1, GETDATE())

then you find the Employee whose StartDate is next month

SELECT *
FROM   Employee
WHERE  DATEPART(MONTH, StartDate) = DATEPART(MONTH, DATEADD(MONTH, 1, GETDATE()))

Upvotes: 3

Matt Bowler
Matt Bowler

Reputation: 191

Something like this will work functionally. The functions in the where clause might cause issues on large data sets though:

declare     @day int = day(getdate())
            ,@month int = month(getdate())

if @month = 12 set @month = 1
else set @month = @month +1

SELECT 
    [columns]
  FROM [table]
where day(Startdate) = @day
and MONTH(Startdate) = @month

Or if you don't care about the day (I think you probably don't) then:

declare @month int = month(getdate())

if @month = 12 set @month = 1
else set @month = @month +1

SELECT 
    [columns]
  FROM [table]
and MONTH(Startdate) = @month

Upvotes: 0

Related Questions