Solved

statement does month of a date fall between....

Posted on 2013-12-19
9
423 Views
Last Modified: 2013-12-19
I have a hire date field-

Need a simple sql statement to select month(hiredate) from employeeinfo where month(hiredate) between month(getdate()) and dateadd(m,3,getdate())

Trying to get all hiredates where the hiredate month falls in between now and 3 months from now.

What would be the correct statement?
0
Comment
Question by:BKennedy2008
9 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39730151
So, what does the query you provided give you?
select month(hiredate) from employeeinfo 
where month(hiredate) between month(getdate()) and dateadd(m,3,getdate())

Open in new window


That assumes, of course, that your employeeinfo table lists the hire dates 3 months into the future.  Are these anticipated hire dates?
0
 

Author Comment

by:BKennedy2008
ID: 39730163
I had that- but it is december, and it only displays Dec months, not Jan or feb months
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39730170
I assume you want the past 3 months

select month(hiredate) from employeeinfo 
where month(hiredate) between month(getdate()) and dateadd(m,-3,getdate())

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:BKennedy2008
ID: 39730199
No-

I have a report where I need to do an annual review with the employees who's hire date's month is from now to 3 months from now.

Ex Hire date 1/14/2010 John smith
hire date 2/1/2008 Joe Smith

Dec-
nobody
Jan-
John Smith
Feb-
Joe Smith
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39730256
so you need to look back to find the month they were hired and find the ones whos anniversary is in the next 3 months
0
 

Author Comment

by:BKennedy2008
ID: 39730263
Yes-  Field is hiredate-

So I need a sql statement that selects those individuals who's hire date is from now through Feb
(Dec, Jan and Feb)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39730272
You need to use IN instead of BETWEEN:


SELECT ...
FROM dbo.employeeinfo ei
WHERE
    MONTH(ei.hiredate) IN (
        SELECT MONTH(DATEADD(MONTH, 0, GETDATE())) UNION ALL
        SELECT MONTH(DATEADD(MONTH, 1, GETDATE())) UNION ALL
        SELECT MONTH(DATEADD(MONTH, 2, GETDATE()))
    )
0
 

Author Comment

by:BKennedy2008
ID: 39730275
something like this:

SELECT EmployeeName + ' ' + [Last Name] as Fullname, HireDate FROM EmployeeInformation
 where DateName(month, Hiredate) >= DateName(Month,month(getdate())) and DateName(month, Hiredate) <= DateName(month, dateadd(m,3,getdate()))
 
 
 order by MONTH(hiredate)

but this is not the correct statement as there are March, June, July records that are included when I execute this
0
 

Author Comment

by:BKennedy2008
ID: 39730285
K-

Thanks. I knew I was missing something
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question