Date Dillema!

Date Dillema!

• Maverickcoder

  Thanks Ben.
  
  You guys have been a great help, I appreciate it alot.
  
  Thanks to all,
  
  Daniel Minnaar
  
  

• PaulB1950

  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
  
  

• Xllerator

  > 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. 
  
  
  

  
  

• big_j

  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
     )
        
  
• Date Dillema!