Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2007
    Posts
    22

    Unanswered: Interesting error

    In this SQL query I get en error like this:
    Incorrect syntax near ','.

    Code:
    SELECT     SUM(mCount * mPrice), CONVERT(DATEPART(yyyy, mDate), varchar) + '.' + CONVERT(DATEPART(mm, mDate), varchar) AS DateSold
    FROM         sales
    WHERE     mDate BETWEEN '9/10/2007 0:0:0' AND '1/10/2008 23:59:59'
    I think you it isnt necessary to show values in the table because it is a syntax error but I dont undertand how it can be incorrect...

    Thanks for help...
    Saren Taşçıyan

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you have your convert arguments backwards. really boring actually. enjoy school this semester,
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2007
    Posts
    22
    yeah, it is very boring but I am learning programming and SQL myself and in 2 websites where I have found the introduction of this function was incorrect...
    I want to ask one more thing.

    If I want to group by second column I write at the end " GROUP BY mDate" but it doesnt group the same values in the second column. Is it possible to say something like this: GROUP BY <second_column>?
    Thanks
    Last edited by Genom; 01-10-08 at 16:19.

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    If you want to group by the values returned in your second column, you need to make sure the group by actually refers to the values in your second column.

    Try GROUP BY CONVERT(DATEPART(yyyy, mDate), varchar)

    well....after correcting your CONVERT syntax error, of course.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT YEAR(mDate)  AS yr
         , MONTH(mDate) AS mth
         , SUM(mCount * mPrice) AS total
      FROM sales
     WHERE mDate >= '2007-09-10'
       AND mDate  < '2008-01-11'
    GROUP
        BY YEAR(mDate)
         , MONTH(mDate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    showoff.


    your message is too short.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Jul 2007
    Posts
    22
    I tried both examplesw from you and the second one worked (from r937). But I had to change lots of code in my program. All this is caused because there isnt date formatting abilitiy in mssql like in msaccess

    Now I get and store the year, month values in different columns so I can group them easily.

    Thanks for your help!!!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Genom
    ... there isnt date formatting abilitiy in mssql like in msaccess
    there is so

    look up the CONVERT function

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

  9. #9
    Join Date
    Jul 2007
    Posts
    22
    you are not as free as like in ms access. In ms access I can write:
    FORMAT([mDate],'yyyy.mm.dd') for example. Now I have to do it in a different way and it makes my job harder in VB.Net.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    access: FORMAT([Tarih],'yyyy/mm/dd')

    sql server: CONVERT(VARCHAR(10),[Tarih],120)

    exactly the same result

    if you don't mind dashes as separators instead of slashes


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

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Genom
    Now I get and store the year, month values in different columns so I can group them easily.
    OMG!!!! That just makes my skin crawl...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Jul 2007
    Posts
    22
    sorry I forgot to add that in my software sometimes I have to do these queries too:
    FORMAT([Tarih],'yyyy/mm/dd')
    FORMAT([Tarih],'yyyy/mm')
    FORMAT([Tarih],'yyyy')

    User selects what kind of query to be done and format changes. But this doesnt exist in mssql as I have seen...

  13. #13
    Join Date
    Jul 2007
    Posts
    22
    Quote Originally Posted by TallCowboy0614
    OMG!!!! That just makes my skin crawl...
    Yeah I know

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Genom
    sorry I forgot to add that in my software sometimes I have to do these queries too:
    FORMAT([Tarih],'yyyy/mm/dd')
    FORMAT([Tarih],'yyyy/mm')
    FORMAT([Tarih],'yyyy')

    User selects what kind of query to be done and format changes. But this doesnt exist in mssql as I have seen...
    Yeah, that stuff is pretty easy. Look up DATE functions and CONVERT function in BOL.

    It's all pretty dang easy if you understand the language (or are willing to do a little poking around for info).

    Ain't nothin' you can do in access (that is worth doing) that you can't do in MS SQL.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Since no one else is being a grumpy purist then I will fulfill the role.

    Ahem.

    Date formatting should be done in your front end application.

Posting Permissions

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