Question

Locked

Help with SQL Query (Date ranges)

By ADynes ·
I've been struggling with a SQL query and can't find a answer anywhere, although I've come kinda close. I need to pull a range of users by hire date BUT just the month and day. For example I want to pull all users who had a hire date in the next 30 days regardless of hire year. So if I was hired July 28, 1992 and I ran this query my name would be included because July 28th is within the next 30 days. I have a view that is on the path to what I want:

SELECT *
FROM dbo.Users
WHERE DATEPART(m, dbo.Users.DateHired) = DATEPART(m, DATEADD(m, 1, GETDATE()))

This gets me everyone in the next month but I need to include the day in there somehow. Any ideas? I'm trying to figure out a "between" statement but can't figure it out where it can span the month.

-Allan

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Try this

by Tony Hopkinson In reply to Help with SQL Query (Date ...

SELECT *
FROM dbo.Users
WHERE DATEPART(dy, GETDATE()) - DATEPART(dy, dbo.Users.DateHired) <= 30

dy is day of year.

Collapse -

Thanks!

by ADynes In reply to Try this

Thats much simplier then what I had planned on using, hoping it still works at the end of the year (for things coming up January 5th for example when its Dec 16). Thanks.

Collapse -

Good point !

by Tony Hopkinson In reply to Thanks!

need to or it with
DatePart(dy,GetDate()) > 335)
and
DatePart(dy,HireDate) + 365 - DatePart(dy,GetDate()) > 30

or somesuch to deal with that.

Collapse -

SQL - Date range query help

by chitandaa In reply to Help with SQL Query (Date ...

Hie i saw your post and i kinda have the same problem. I am trying to create a query that'll show me records for 7 days from the current date, i am using MS SQL Server 2005. Any help would be appreciated .... i am just an amateur so please HELP!! You can email me at chitandaa@yahoo.com.

Collapse -

Try

by Tony Hopkinson In reply to SQL - Date range query he ...

Select * From MyTable Where MyDateField >= DateAdd(day,-7,GetDate())

or some such

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums