none
Query 8 Days ago based on system date

    Question

  •  

    Hi,

     

    I'm trying to query an SQL table column with date values to show 8 Days ago results.

     

    I've started with this query:

     

    SELECT     ficheiro, erro, descritivo_erro, contrato, DO, movimento, data, descritivo, tipo_movimento, desconto, montante, comissao, IVA
    FROM         status_day
    WHERE     (YEAR(data) = YEAR(GETDATE())) AND (MONTH(data) = MONTH(GETDATE())) AND (DAY(data) = DAY(GETDATE()) -8)
    ORDER BY descritivo_erro, contrato

     

    The problem is that the text in red will have some problems when the month changes - If I want the 8 days ago results from January and the system date is 1st of February the query will not return any values.

     

    I read something about DATESERIAL but is wasn't conclusive on how to use it with system date.

     

    Please help me out with this query.

    Wednesday, January 16, 2008 4:34 PM

All replies

  • Although I wouldn't do it this way, I think the problem you are currently having is everywhere you have GETDATE() you need the "-8", not just for the day part
    Wednesday, January 16, 2008 4:39 PM
  • Actually that will give me 8 years back, 8 months back and 8 days back. And the problem will not be solved.

     

    When we execute the query mentioned what is happening is that SQL is taking out 8 from the Day number on the Date field and not going back 8 days. On the 1st (or 2nd or 3rd...) day of every month you have: 3 - 8 and therefor no results show.

     

    How do we get the query to manke it in terms of days and not numbers ?

     

    Wednesday, January 16, 2008 4:50 PM
  • What? 8 years?

    Code Block

    DECLARE @Test datetime

    SET @Test = '2/1/08'

    SELECT @Test - 8 --2008-01-24 00:00:00.000


    Wednesday, January 16, 2008 4:53 PM
  •  

    Hi,

     

    One was you could do it is use the DATEADD function.  (Sorry, if you have already thought of this, but there is a reason why you can't use it).

     

    I assume that your data column is a datetime column

     

    Code Block

    SELECT     ficheiro, erro

             , descritivo_erro

             , contrato

             , DO

             , movimento

             , data

             , descritivo

             , tipo_movimento

             , desconto

             , montante

             , comissao

             , IVA
      FROM     status_day

     WHERE     CONVERT(CHAR(8), data, 112) = CONVERT(CHAR(8), DATEADD(dd, -8, GETDATE()), 112)

     

    If your data is an int format YYYYMMDD then leave the convert off the data column and it will still work.

     

    Hope that helps,

     

    Chris

     

     

     

    Wednesday, January 16, 2008 4:54 PM
  •  

    the prob is that you fragment each date part then u compare , haven't you tried to compare the whole date value as one unit :

     

    some thing like that data

     

    where   data = DATEADD(day,-8,GETDATE())

     

    now you compare your column value to 8 days earlier

     

    if you still want to use yor method you can try:

     

    Where YEAR(data) = YEAR(DATEADD(day,-8,GETDATE())) AND (MONTH(data) = MONTH(DATEADD(day,-8,GETDATE())) AND DAY(data) = DAY(DATEADD(day,-8,GETDATE()))

     

    or easier you can store the value of the new date as

     

    select @new_date = DATEADD(day,-8,GETDATE())

     

    and use @new_date in your statement

     

    Regards
    Wednesday, January 16, 2008 4:56 PM
  • Try this...

    SELECT ficheiro, erro, descritivo_erro, contrato, DO, movimento, data, descritivo, tipo_movimento, desconto, montante, comissao, IVA
    FROM   status_day
    WHERE  Data >= DateAdd(Day, DateDiff(Day, 0, GetDate()-8), 0)
           And Data < DateAdd(Day, DateDiff(Day, 0, GetDate()-7), 0)
    ORDER BY descritivo_erro, contrato


    All of that DateAdd/DateDiff stuff can seem a little complicated, but if you run each one separately, you'll see that it returns the date you want.


    Select DateAdd(Day, DateDiff(Day, 0, GetDate()-8), 0) -- 8 Days ago
    Select DateAdd(Day, DateDiff(Day, 0, GetDate()-7), 0) -- 7 Days ago

    In this case, you are better off leaving the query the way I wrote it.  If you have an index on the Data column (and you should to speed up this query), the way I wrote the WHERE clause will allow SQL Server to use an index seek (instead of a slower scan).

    Make sense?
    Wednesday, January 16, 2008 4:59 PM