1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to convert date and currency in SQL statement

Discussion in 'C#' started by becky1, Jul 4, 2007.

  1. #1
    Pls Give me quick reply by glancing on this statement what you see wrong in this. I am having problem with a statement for count of number of pieces ordered in less than a date specified. Statement is :::
    set rs = cn.execute("select SUM(pieces) from orders where convert(varchar(10),test.[Min_Price]) >'"& MinPrice&"'and date_ordered >'"& Dateordered& "' ")

    Test is table
    Min_Price in Ms SQL is data type "Currency"
    And Pieces field is data type "Float"
    And date_ordered is Data type "smalldatetime"
    I requested th above two from the form and trying to input the values for this SQL statemnt but either statement is not right or i am not converting the currency & date fields.
    What will be the right statement with conversion of currency filed and datetime field for this statement will be for asp in MS SQL server db.


    set rs = cn.execute("select SUM(pieces) from orders where convert(varchar(10),test.[Min_Price]) > '"& MinPrice&"'and date_ordered >'"& Dateordered& "' ")
     
    becky1, Jul 4, 2007 IP
  2. dizzy

    dizzy Peon

    Messages:
    43
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    The whole statement is messed up.
    In order to get a sum, you gave to group by a field such as date, category, item, etc.

    http://msdn2.microsoft.com/en-us/library/ms187810.aspx

    Also, if the min price is coming from the 'test' table can use a subquery.

    Try something like this:
    SELECT SUM(Pieces) AS PiecesSold,date_ordered AS Date FROM orders WHERE Price > (SELECT MinPrice FROM test) GROUP BY date_ordered

    It should give you the number of pieces sold for each day for order that was above the MinPrice.

    Hope this helps.
     
    dizzy, Jul 6, 2007 IP
  3. malkassem

    malkassem Peon

    Messages:
    36
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Give this a shot. You do not need to do any conversions.

    set rs = cn.execute("select SUM(pieces) from orders where test.[Min_Price] > " & MinPrice & " and date_ordered > '" & Dateordered & "'")
     
    malkassem, Jul 8, 2007 IP