0

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

3
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by bill51
0

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.

0

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/__'

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.