Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: date need back off four year

    I have a table in sql server, i need to import this table to another
    database in same sql server using DTS, In the table, we have a field called
    'qualDate', I need to import the record that the qualDate is in the date of
    today and back off four years, for example, today is 8/10/2004, back off four
    year should be 8/10/2000, so i need only the record that qualDate is between
    8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
    should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can use the expression DateAdd(year, -4, GetDate()) in order to find the date four years ago. Without knowing a lot more about your table structures, etc. I can't make a good guess at what code you'll need.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    thanks pat, i am using DTS and schedule to import the table to another database every night. My table has fields: Name, Address, County, QualityDate. Quality is short date type. Is that good for you to figure out when i create job how to write a query in where clause, such as, select Name, Address, County, QualityDate from table1 where ....... (i don't know how to do it) .Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This won't be absolutely perfect, but you could get really close using:
    Code:
    SELECT Name, Address, County, QualityDate
       FROM SourceServer.SourceDatabase.dbo.SourceTable
       WHERE  QualityDate
          BETWEEN Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121)
          AND Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121) + ' 23:59'
    That snippet will pick up the rows that occured anytime on the day that is four years ago today. This should work Ok for 90+ years, which will be well past the point that SMALLDATETIME can represent!

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    660
    thanks pat, i got it. Have a nice day!

Posting Permissions

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