Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: Fighting with Dates trying to lookup month and year

    Hi all I this stored procedure is suppose to turn give the users the ability to put the month and date in so they can see how many each inspector did for that month and year.



    Code:
    Create Procedure LookupYearMonth
    AS
    SELECT I.[Last Name], I.[First Name], 
        [Date by Month] = DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE]), 
        [Count Of Main Table] = count(*)
     FROM Inspectors I
        INNER JOIN [Main Table] MT
           ON (I.ID = MT.Inspector)
     GROUP BY I.[Last Name], I.[First Name], 
        DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE])
     WHERE (MT.[Date] >= CONVERT(datetime, @EnterMonthYear)) AND 
        (MT.[Date] < DATEADD(month, 1, CONVERT(datetime, @EnterMonthYear)))
    
    GO

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please tell us what the datatypes of the columns are

    please show us some sample data

    please tell us what happens when you run the procedure

    this year i did not renew my license fee for Microsoft&#174; CrystalBall&#169;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Sorry R937

    I'm sorry about I will post the datatypes and the results I get

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dude, nobody PAYS for Microsoft® CrystalBall©. There are plenty of hacked versions floating around on the web.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    what happens when you use a cracked version? will you get cut?

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    My cracked crystal ball is obscuring my view but it seems you do
    select convert(datetime,042007)
    which will return you a date for the number of days since 1 Jan 1900
    i.e. the same as
    select dateadd(dd,042007,'1 Jan 1900')

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so i went out and downloaded an Open Sores crystal ball, and it says...

    1. you are missing a parenthesis here --

    DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE])

    2. the WHERE clause is in the wrong spot
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Does the Open Sores Crystal Ball run on Lie-nyuks?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that particular mispronunciation is ~so~ common!!

    it is pronounced Lin-nyuks!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Data types

    [Date](datetime)
    Inspector(nvarchar)(75)
    LastName(nvarchar)
    FirstName(nvarchar)


    when I execute it I get a "Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near '='."

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have you counted your parentheses yet?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    did that

    I did that I added a parenthesis to
    like you said earlier.

    Code:
    (DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE]))

  13. #13
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I see three on the left and two on the right.

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    oh good grief

    there has to be an easier way of doing this, there just has to be

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    An easier way to count to three? Hmmm....there's a challenge.

    Seriously, I check parenthesis like this:
    Start at the left with the number zero and move towards the right. Add one to your mental counter for each left parenthesis and subtract one for each right parentheses. (Notice, left-hand ones are spelled with an "is", and right-hand ones are spelled with an "es" (to tell them apart))). When you get to the end of the line, you should be back to zero.
    Also, avoid using (unnecessary) parenthesis. They do not (always) make the context clearer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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