CODEWITHSUNDEEP
sqlsql-server
MANISH KISHORE
MANISH KISHORE

Reputation: 47

Find Employees who are not promoted in a specific [Month] in SQL Server

I have a table called tblemployees, in that table I want an output of those employees who are not promoted in a specific month.

Create table tblEmployees
(
     Id int Identity Primary key,
     Name nvarchar(40),
     PromotedMonth int
)

----------------------------
| Id | Name | PromotedMonth |
|----|------|---------------|
| 1  | Mark |     03        |
| 2  | Mary |     12        |
-----------------------------

I want output like this:

----------------------------
| Id | Name | PromotedMonth |
|----|------|---------------|
| 1  | Mark |     March     |
| 2  | Mary |     December  |
-----------------------------

Every time I use DatName function, it always returns January as [PromotedMonth]

So how can I do that?

How to figure out which employee is promoted in a specific month?

Upvotes: 2

Views: 332

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use datename() function to get month name :

select *, datename(month, dateadd(mm, PromotedMonth-1, 0)) 
from tblEmployees e;

No need to use cast() to do datetime conversation. Just use 0 as unspecified date component to set default date 1900-01-01.

Upvotes: 0

D-Shih
D-Shih

Reputation: 46249

You can try to use DATENAME function.

TestDDL

CREATE TABLE T(
  ID INT,
  NAME VARCHAR(10),
  PromotedMonth INT
);

INSERT INTO T VALUES (1,'T',3);
INSERT INTO T VALUES (2,'T1',12);

Query

SELECT id,
       name,
       DATENAME(month, DATEADD(month, PromotedMonth -1 , CAST('1900-01-01' AS datetime))) AS 'PromotedMonth'
FROM T

Result

| id | name | PromotedMonth |
|----|------|---------------|
|  1 |    T |         March |
|  2 |   T1 |      December |

sqlfiddle:http://sqlfiddle.com/#!18/69b30/3

Upvotes: 2

Related Questions