sql update query

[Sinha's]

sir,i i have two date columns today_date and doj_date.both of them are varchar data type.now i want to update today_date='01/06/2010' to '01/04/2010' where doj_date='01/04/2010'.that means only the month column. ihave used the following code
update installment_scheme_customer set substring(today_date,4,2)='04' where substring(today_date,4,2)='06' and substring(doj_date,4,2)='04' .
but it is showing error like this
Incorrect syntax near ','.

please sir,can u help me in solving the problem

[aquilax]

Try something like this:

UPDATE installment_scheme_customer 
SET today_date = REPLACE(today_date,substring(today_date,3,4),'/04/') 
WHERE substring(today_date,4,2)='06' 
AND substring(doj_date,4,2)='04'

And use Date for date type. It's much easier to work with.

[bill51]

How about:
UPDATE installment_scheme_customer
SET today_date = REPLACE(today_date,'/06/','/04/')
WHERE substring(doj_date,4,2)='04';
or alternative where clause
WHERE doj_date like '??/04/??'
Not done access for a while so I think ? is the correct wildcard for simgle required character! equivalent of sql server '__/04/__'