sMartket sMartket - 1 year ago 64
SQL Question

How to Split SQL column and get a part at condition

I am use SQL Server 2008 in this solution.

I have a table tbltrans:

ID money datepay (nvarchar(20))
1 10 21/10/2016

SQL Update command:

UPDATE dbo.tbltrans
SET money=0
WHERE RIGHT(datepay, 7) = '10' AND RIGHT(datepay, 4) = '2016'

this command not return any error and not update column money.

please help fix or any solution the same.


Answer Source

EDIT: See John Cappelletti's answer above, converting that text to a DATE will make everything easier.

Yeah, you'd have to use a substring of sorts to parse that text. You can check that it is not doing what you'd like in a basic SELECT:

SELECT TOP 100 RIGHT(datepay, 7), RIGHT(datepay, 4) FROM dbo.tbltrans

If it's a date field, date functions will do what you want:

UPDATE dbo.tbltrans
SET money=0 
WHERE MONTH(datepay) = 10 AND YEAR(datepay) = 2016

If it's not a DATE you may have to CAST/CONVERT first:

UPDATE dbo.tbltrans
SET money=0 
WHERE MONTH(CONVERT(DATE,datepay,103)) = 10 AND YEAR(CONVERT(DATE,datepay,103)) = 2016
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download