Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts

    Dates before 30 days

    Friends,

    I have this sql statement:

    SELECT TblAnalista.NomeDoAnalista, TblEmpresa.NomeDaEmpresa, TblProduto.NomeDoProduto, TblPassosStatus.Passo, Nz([DataReprogramada],[DataProgramada]) AS DataDue, TblPassosStatus.C
    FROM ((TblAnalista LEFT JOIN TblEmpresa ON TblAnalista.CodAnalista=TblEmpresa.CodAnalista) LEFT JOIN TblProduto ON TblEmpresa.CodEmpresa=TblProduto.CodEmpresa) LEFT JOIN TblPassosStatus ON TblProduto.CodProduto=TblPassosStatus.CodProduto
    WHERE (((Nz([DataReprogramada],[DataProgramada]))<Date()) AND ((TblPassosStatus.DataRealizada) Is Null) AND ((TblProduto.[Ativado])=Yes))
    ORDER BY Nz([DataProgramada],[DataReprogramada]);


    WHERE I have provided all dates less than today.
    For example, How do I select only dates overdue for more than
    30 days, ie, all the dates before March 20?

    Thank you.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I think you should be able to use:
    Date()-30

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I always prefer to use the DateDiff function in similar situations.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In Access simple arithmetic on dates works OK (e.g. Date()-30) but I believe that this is not true with SQL Server.
    I only every work with Access so I usually just use the arithmetic method, but I can understand why people who move between Access and SQL Server would probably use a method that works equally well in both - DateDiff.
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    patt, ruirib and johnhutchison thanks for responding.
    Sorry, I drew up wrong, I'm trying two criteria. I'm trying to dates before today between 15 and 30 days. For example, dates from March 19 until April 4, ie 30 days before until 15 days before today.
    I do not know how to use almost exactly DateDiff.
    Thank you.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    patt, ruirib and johnhutchison, thank you for help. I got using Between.


    WHERE Nz ([DataReprogramada], [DataProgramada]) Between Date () - 30 And Date () - 15 AND TblPassosStatus.DataRealizada Is Null AND TblProduto. [Enabled] = Yes

    Again, thank you.

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Ok, glad it's sorted .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •