[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2013-12-19
9
Medium Priority
?
453 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

650 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