Results 1 to 4 of 4

Thread: Updating a view

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Updating a view

    I don't know if this is possible but, I have a view that retrieves data between 2 dates. The data is then exported to an Excel spreadsheet. My question is this: Is there some way I can automatically change the date in my alter view window? For example, I want to extract the data at the beginning of each month for the previous month. Then whoever is looking at the data can then refresh every month to get the previous months data. I want something that will automatically update my view every month with the previous month, without me having to go in and physically doing it. Is there a way, and how complicated would it be?
    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It sounds like you need a where clause on your view that includes the first and last days of the previous month?

    Actually, it may be easier than that. How about

    select dateadd(m, -1, dateadd(dd, -1 * datepart(dd, getdate()), getdate())) , dateadd(dd, -1 * datepart(dd, getdate()), getdate())

    Select is being used here to show the values. You will use these two values in a between statement. Still need a way to strip off the time, but this should get you started, I hope. Let me know if you need more.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm. Easier to strip off time than I thought.... Also, I found a small addition error...

    select convert(datetime, convert(varchar(10), dateadd(m, -1, dateadd(dd, 1 + -1 * datepart(dd, getdate()), getdate())), 101 )), convert(datetime, convert(varchar(10), dateadd(dd, 1+ -1 * datepart(dd, getdate()), getdate()), 101 ))

  4. #4
    Join Date
    Aug 2003
    Posts
    328
    I think I'm close to what I need except the dates I need are dates from a certain column in my view. How can I extract these dates from a date field?
    Thanks for your time.

Posting Permissions

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