Solved

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

Posted on 2013-12-19
9
438 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

717 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