Professional Web Applications Themes

DATEPART error message - Microsoft SQL / MS SQL Server

Hello, I have a problem using the DATEPART function. The error message returned is 'invalid parameter 1 specified for datepart'. This is my query (object: return the sum of the invoices per month per lot) SELECT L.DESCRIPTION_LOT AS [LOT], DATEPART ('mm', F.DATEINVOICE) + '/' + DATEPART ('yyyy', F.DATEINVOICE) AS [MONTH], SUM (CC.TOTAL_AMOUNT) AS [TOTAL INVOICE] FROM dbo.TDLOT AS L dbo.TDCORRORDER CC dbo.TDDETAILCORRORDER DCC dbo.TDINVOICES AS F GROUP BY L.DESCRIPTION_LOT , DATEPART ('mm', F.DATEINVOICE) + '/' + DATEPART ('yyyy', F.DATEINVOICE), SUM (CC.TOTAL_AMOUNT) HAVING F.DATEINVOICE BETWEEN '01/04/2001' AND '30/04/2004' WHERE (L.IDLOT = CC.IDLOT) AND (CC.IDCORRORDER = DCC.IDCORRORDER) AND (DCC.IDINVOICE = F.IDINVOICE) UNION ...

  1. #1

    Default DATEPART error message

    Hello,

    I have a problem using the DATEPART function.
    The error message returned is 'invalid parameter 1
    specified for datepart'.
    This is my query (object: return the sum of the invoices
    per month per lot)

    SELECT
    L.DESCRIPTION_LOT AS [LOT],
    DATEPART ('mm', F.DATEINVOICE) + '/' + DATEPART
    ('yyyy', F.DATEINVOICE) AS [MONTH],
    SUM (CC.TOTAL_AMOUNT) AS [TOTAL INVOICE]
    FROM
    dbo.TDLOT AS L
    dbo.TDCORRORDER CC
    dbo.TDDETAILCORRORDER DCC
    dbo.TDINVOICES AS F
    GROUP BY
    L.DESCRIPTION_LOT ,
    DATEPART ('mm', F.DATEINVOICE) + '/' + DATEPART
    ('yyyy', F.DATEINVOICE),
    SUM (CC.TOTAL_AMOUNT)
    HAVING
    F.DATEINVOICE BETWEEN '01/04/2001' AND '30/04/2004'
    WHERE
    (L.IDLOT = CC.IDLOT) AND (CC.IDCORRORDER =
    DCC.IDCORRORDER) AND (DCC.IDINVOICE = F.IDINVOICE)

    UNION

    SELECT
    L.DATEINVOICE,
    DATEPART ('mm', F.DATEINVOICE) + '/' + DATEPART
    ('yyyy', F.DATEINVOICE),
    SUM (CP.TOTAL_AMOUNT)
    FROM
    dbo.TDLOT AS L
    dbo.TDPREVORDER CP
    dbo.TDDETAILPREVORDER DCP
    dbo.TDINVOICES AS F

    GROUP BY
    L.DESCRIPTION_LOT ,
    DATEPART ('mm', F.DATEINVOICE) + '/' + DATEPART
    ('yyyy', F.DATEINVOICE),
    SUM (CP.TOTAL_AMOUNT)

    HAVING
    F.DATEINVOICE BETWEEN '01/04/2001' AND '30/04/2004'

    WHERE
    ((L.IDLOT = CP.IDLOT) AND (CP.IDPREVORDER =
    DCP.IDPREVORDER) AND (DCP.IDINVOICE = F.IDINVOICE))
    ORDER BY
    [LOT],
    [MONTH]


    Thanks for the help.
    Andy
    P.S. I'm a beginner...
    Andy Guest

  2. #2

    Default Re: DATEPART error message

    Not DATEPART ('mm', F.DATEINVOICE) just DATEPART (mm, F.DATEINVOICE)

    "Andy" <com> wrote in message
    news:083a01c35b34$97222450$gbl... 


    Alex Guest

  3. #3

    Default DATEPART error message

    Andy, try using

    DATEPART (MONTH, F.DATEINVOICE) 
    AND '30/04/2004' 
    AND '30/04/2004' 
    F.IDINVOICE)) 
    Peter Guest

  4. #4

    Default Re: DATEPART error message

    additionally your year format 'yyyy' should be yyyy. below are all the
    abbreviations from BOL
    Datepart Abbreviations
    year yy, yyyy
    quarter qq, q
    month mm, m
    dayofyear dy, y
    day dd, d
    week wk, ww
    weekday dw
    hour hh
    minute mi, n
    second ss, s
    millisecond ms


    --
    BR,

    Mark Broadbent mcdba,mcse+i
    _________________________
    "Alex Cieszinski" <com> wrote in message
    news:e%phx.gbl... 
    >
    >[/ref]


    Mark Guest

Similar Threads

  1. How To Supress Acrobat Error Message And Alert Message
    By ckpang@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 10
    Last Post: January 15th, 11:33 AM
  2. Replies: 4
    Last Post: March 23rd, 12:56 AM
  3. DatePart query
    By David in forum ASP
    Replies: 3
    Last Post: September 17th, 01:11 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139