Member Avatar for 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

  • 3 Contributors
  • 2 Replies
  • 119 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by bill51

Dani AI

Generated

Quick answer and why the original error happened: SQL does not let you assign to a substring expression. The call you tried returns a value; it is not a target for assignment. You must set the whole column to an expression that produces the new string (for string fixes) or, better, work with proper date values.

A safe short fix (string-level), when every value is reliably dd/MM/yyyy, is to replace the two-month characters with STUFF(...) while restricting rows to only those with valid dates. This avoids the "assign to substring" error and limits changes to the intended rows:

UPDATE installment_scheme_customer
SET today_date = STUFF(today_date, 4, 2, '04')
WHERE TRY_CONVERT(date, doj_date, 103) IS NOT NULL
  AND MONTH(TRY_CONVERT(date, doj_date, 103)) = 4
  AND MONTH(TRY_CONVERT(date, today_date, 103)) = 6;

Longer‑term, follow 's good advice and convert the columns to real DATE types. Steps: add a DATE column, populate it with TRY_CONVERT(...,103), inspect any rows that fail conversion, then reconstruct dates with DATEFROMPARTS(year, newMonth, day) (this avoids brittle string manipulations). Example outline:

ALTER TABLE installment_scheme_customer ADD today_dt date;
UPDATE installment_scheme_customer
SET today_dt = TRY_CONVERT(date, today_date, 103);

-- then change month in the date column
UPDATE installment_scheme_customer
SET today_dt = DATEFROMPARTS(YEAR(today_dt), 4, DAY(today_dt))
WHERE MONTH(TRY_CONVERT(date, doj_date, 103)) = 4
  AND MONTH(today_dt) = 6;

Notes and cautions: 's REPLACE is simple but can misfire if the pattern appears elsewhere. Changing month by rebuilding a date can fail when the day does not exist in the new month (e.g., 31 -> April); check or normalize those rows first. Always test your WHERE with a SELECT first, run updates inside a transaction or on a copy, and keep a backup. For reference: STUFF, TRY_CONVERT and DATEFROMPARTS are documented by Microsoft.

Recommended Answers

All 2 Replies

Member Avatar for 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.

Member Avatar for 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/__'

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.