The datepart function requires 2 arguements..

• mattech06

  SSCrazy

  Points: 2484

  More actions

  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,

• andyscott

  Ten Centuries

  Points: 1264

  More actions

  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?

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  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" 😉

• mattech06

  SSCrazy

  Points: 2484

  More actions

  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.