Web Development

Ask something
Question Locked

Help with SQL Query (Date ranges)

0 helpful
6 replies

by adynes

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

6 replies

by tony hopkinson

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

dy is day of year.

by adynes

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.

by tony hopkinson

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

or somesuch to deal with that.

by chitandaa

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.

by tony hopkinson

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

or some such

This discussion is closed to new replies.