Date Dillema!

Hi There,

I've recently come across a problem with a query I'm trying to get results out of.

Here's the situation:

I have a table called 'ProductsChild', which just stores information about Books such as: ReleaseDate, ISBN Number, etc.

I need to know if a specific book is a 'New Release'. A new release is a book that has not been published yet (Release Date) and is still a new release after 1 month of it being published already.

I used this query:

SELECT *
FROM ProductsChild pc INNER JOIN
ProductsParent pp ON pc.ProductsParentID = pp.ID
WHERE (MONTH(pc.ReleaseDate) >= MONTH(GETDATE()) - 1) AND (YEAR(pc.ReleaseDate) >= YEAR(GETDATE()))

I need to join these 2 tables because I require information from both of them on my front end application.

The problem is that any book that is to be released in a future year, doesn't get returned.

Please help if you can, thanks.You can achieve that with functions like DATEADD and DATEDIFF. They will correct calculate your date including year, month and so on..

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp|||

> I need to know if a specific book is a 'New Release'. A new release is a

> book that has not been published yet (Release Date) and is still a new

> release after 1 month of it being published already.

DECLARE @.cutoff SMALLDATETIME

SET @.cutoff = DATEADD(MONTH, -1, DATEDIFF(DAY,0,CURRENT_TIMESTAMP))

SELECT ...

WHERE pc.ReleaseDate >= @.cutoff

> SELECT *

> FROM ProductsChild pc INNER JOIN

> ProductsParent pp ON pc.ProductsParentID = pp.ID

> WHERE (MONTH(pc.ReleaseDate) >= MONTH(GETDATE()) - 1) AND

> (YEAR(pc.ReleaseDate) >= YEAR(GETDATE()))

>

> I need to join these 2 tables because I require information from both of

> them on my front end application.

>

> The problem is that any book that is to be released in a future year,

> doesn't get returned.

Well, think about it for a moment. You're saying the month has to be >= 10

and the year has to be >= 2005. Don't you think one of those conditions

will fail for a book that is published in January of 2006? Also, what

happens when you run this query on January 2nd? Suddenly all the books for

the current year and the previous month will no longer come back (your

conditions will now be month >= 12 and year >= 2006).

I don't think it's a good idea to separate the different date parts in this

kind of query. It is really better suited to a range query like I have

constructed above... particularly if there is an index on releaseDate.

|||The other thing to consider is if you have any books that do not have a release date (release date is null). They will not be part of your result set. Here is a solution that will include those if you have any.

SELECT *
FROM ProductsChild pc
INNER JOIN ProductsParent pp ON pc.ProductsParentID = pp.ID
WHERE
(
pc.ReleaseDate >= DATEADD(M, -1, GETDATE())
OR pc.ReleaseDate IS NULL
)
|||Thanks Ben.

You guys have been a great help, I appreciate it alot.

Thanks to all,

Daniel Minnaar