?
Solved

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

Posted on 2013-12-19
9
Medium Priority
?
463 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.
Suggested Courses

599 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