Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    26

    Unanswered: Stating DATEs in a view

    Good day all.

    Trying to automate a process by using this code for the DATE criteria in a view:

    BETWEEN CONVERT (varchar, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 15), 112)
    AND CONVERT (varchar, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 14), 112)

    however, the view comes to a grinding halt! If I specifically use

    BETWEEN 20111016 AND 20111115

    for the date criteria then the view displays the results perfectly, however I do not want to edit the date values on a monthly basis so is there another way of coding for those dates that SQL 2005 will digest easily?

    Any help greatly appreciated!
    Thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why are you using CONVERT?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    26
    That line is some code I picked up elsewhere. The value is stored as a DECIMAL Datatype but in the YYYYMMDD format.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your "code i picked up elsewhere" is not appropriate

    try it again, only without the CONVERT functions

    oh, and which data is stored as DECIMAL?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2006
    Posts
    26
    The date is stored as decimal, for example "20111117", not as DATETIME Datatype.

    I have since discovered that the View is running that code for the entire set of records in the source table, and not just the records I want to return as a result. I did try to use Variables for the criteria and it worked perfectly, but I can't save that as a view as I had hoped.
    Creating a SPROC for it would be an option if only it allows an Access User who does not have SQL 2005 installed on their machine the ability to kick the sproc into action. I'm not sure if possible or how to go about doing this.

    I shall try 'DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 15), ' as the criteria.

    Thanks for your input on this.
    Last edited by VegaLA; 11-17-11 at 13:48.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by VegaLA View Post
    The date is stored as decimal, for example "20111117", not as DATETIME Datatype.
    not ideal, but we can still work with this, and what's more, make it indexable and super efficient

    what you want is to do this --

    ... BETWEEN some_decimal_value AND some_other_decimal_value

    it's as simple as that

    which is why i asked how come you were converting to VARCHAR

    a VARCHAR column could've been indexed, i believe, but i guess it was doing a conversion from DECIMAL to VARCHAR and this caused a table scan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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