Post reply

The datepart function requires 2 arguements..

  • March 3, 2015 at 6:51 am

    #305042

    Hi,

    I have this sql and keep getting 'The datepart function requires 2 arguments' when I check the syntax of the whole statement

    WHERE

    r.dbAddDate >= DATEPART(dd,'2014-01-01',

    (DATEPART(mm,(CAST('2015 February' AS DATETIME)),

    (DATEPART(dd,'2014-01-01')))))

    I've messed round with it and still get the same error - is this sql even possible? Is that why I'm getting it?

    Here's the whole statement

    SELECT

    r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName,

    SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType !=29 AND t.LedgerType !=30 AND t.LedgerType != 31 AND t.LedgerType != 1 OR t.LedgerType = 16 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,

    SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 AND t.LedgerType != 16 AND t.LedgerType != 45 OR t.LedgerType = 29 OR t.LedgerType = 30 OR t.LedgerType = 31 THEN t.LedgerAmount ELSE 0.00 END) AS Payments,

    SUM(CASE WHEN t.LedgerType = 1 OR t.LedgerType = 46 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS Contracts

    FROM

    vw_ReferralKPIs r

    LEFT JOIN Transactions t ON t.PatientID = r.dbPatID AND t.ClientRef = r.ClientRef

    LEFT JOIN Patient p ON p.dbPatID = r.dbPatID AND p.ClientRef = r.ClientRef

    WHERE

    --(r.dbAddDate >= (CAST('2015 February' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'2015 February'))

    r.dbAddDate >= DATEPART(dd,'2014-01-01',

    (DATEPART(mm,(CAST('2015 February' AS DATETIME)),

    (DATEPART(dd,'2014-01-01')))))

    --r.dbAddDate <= (DATEPART(dd,'2015-03-03 23:59') AND DATEPART(mm,(CAST('2015 February' AS DATETIME) AND DATEPART(dd,'2015-03-03 23:59')))

    --AND r.dbAddDate <= DATEPART(dd,'2015-03-03 23:59',

    -- (DATEPART(mm,(CAST('2015 February' AS DATETIME)),

    -- (DATEPART(dd,'2015-03-03 23:59')))))

    --AND (r.dbStaffLastName IN ('') OR '' = '')

    --AND (r.LastName IN ('Website ') OR 'Website ' = '')

    --AND r.ClientRef = 'EPS'

    GROUP BY

    r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName

    thanks,

  • March 3, 2015 at 7:22 am

    #1781347

    Can I take it you're trying to compare a date in a table with another that you're constructing on the fly?

    i.e. that r.dbAddDate >= some date?

    But "some date" in your query just looks like 2014-01-01 - so why don't you just make the condition r.dbAddDate>='2014-01-01'?

    Or where does the "February" come into it?

  • March 3, 2015 at 8:22 am

    #1781370

    WHERE

    r.dbAddDate >= DATEPART(dd,'2014-01-01'),

    (DATEPART(mm,(CAST('2015 February' AS DATETIME)),

    (DATEPART(dd,'2014-01-01')))))

    you've missed out a closing ) as indicated above

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • March 3, 2015 at 10:04 am

    #1781436

    thanks. actually ended up using a much simpler solution in the end where I didn't need to use DATEPART.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply