Solved

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

Posted on 2013-12-19
9
432 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

696 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