March 3, 2015 at 6:51 am
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
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
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
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy