You are welcome.
Method2 creates a date column recordset of rowid,date then loops through each element and updates the associated row with new date.
#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING
FUNCTION PBMAIN ()
CreateTestDatabase "Method1 Update by the month"
Method1
CreateTestDatabase "Method2 Loop through recordset"
Method2
? gs
END FUNCTION
'-----------------------------------------------------------------------------------------------------
SUB Method1 'Scan for dates in a month, then update. Perform once for each of the 12 months.
slOpen "junk.db3"
slexe "update t1 set c1 = substr(c1,-4) || '-01-' || substr(c1,-8,2) where instr(c1,'January') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-02-' || substr(c1,-8,2) where instr(c1,'February') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-03-' || substr(c1,-8,2) where instr(c1,'March') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-04-' || substr(c1,-8,2) where instr(c1,'April') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-05-' || substr(c1,-8,2) where instr(c1,'May') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-06-' || substr(c1,-8,2) where instr(c1,'June') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-07-' || substr(c1,-8,2) where instr(c1,'July') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-08-' || substr(c1,-8,2) where instr(c1,'August') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-09-' || substr(c1,-8,2) where instr(c1,'September') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-10-' || substr(c1,-8,2) where instr(c1,'October') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-11-' || substr(c1,-8,2) where instr(c1,'November') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-12-' || substr(c1,-8,2) where instr(c1,'December') > 0"
LOCAL sArray() AS STRING
slselAry "select rowid, c1 from t1",sArray(),"Q9c"
gs+=JOIN$(sArray(),$CR) + STRING$(3,$CR)
slClose
END SUB
'-----------------------------------------------------------------------------------------------------
SUB Method2 'Create recordset of dates. Update date column from first to last record.
LOCAL eleNum,num AS LONG,sNewDate,sArray() AS STRING
DIM sMonthName(1 TO 12) AS STRING
DIM sMonthNum (1 TO 12) AS STRING
ARRAY ASSIGN sMonthName() = "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"
ARRAY ASSIGN sMonthNum () = "01", "02" ,"03" ,"04" ,"05" ,"06" ,"07" ,"08" ,"09" ,"10" ,"11" ,"12"
slOpen "junk.db3"
LOCAL sArray() AS STRING
slSelAry "select rowid,c1 from t1",sArray(),"Q9c"
FOR eleNum = 1 TO UBOUND(sArray)
FOR num = 1 TO 12
IF INSTR(sArray(eleNum),sMonthName(num)) THEN
sNewDate = RIGHT$(sArray(eleNum),4) + "-" + sMonthNum(num) + "-" + MID$(sArray(eleNum),LEN(sArray(eleNum))-7,2)
'slexe "update t1 set c1 = '" + sNewDate + "' where rowid =" + STR$(eleNum) 'wrong 7/4/18 3:45 PM CST
slexe "update t1 set c1 = '" + sNewDate + "' where rowid =" + STR$(VAL(sArray(eleNum)))
EXIT FOR
END IF
NEXT
NEXT
slSelAry "select rowid, c1 from t1",sArray(),"Q9c"
gs+=JOIN$(sArray(),$CR) + STRING$(3,$CR)
slClose
END SUB
'------------------------------------------------------------------------------
SUB CreateTestDatabase(sTitle AS STRING)
KILL "junk.db3"
'IF ERR = 0 THEN ? "Kill junk.db3":ERRCLEAR ELSE ? "Kill error" + STR$(ERRCLEAR)
slopen "junk.db3","C"
slexe "create table if not exists t1(c1)"
slexe "insert into t1 values('Friday, June 29, 2018')"
slexe "insert into t1 values('Saturday, June 30, 2018')"
slexe "insert into t1 values('Sunday, July 01, 2018')"
slexe "insert into t1 values('Monday, July 02, 2018')"
slexe "insert into t1 values('Tuesday, July 03, 2018')"
slexe "insert into t1 values('Unknown, December 25, 2018')"
LOCAL sArray() AS STRING
slSelAry "select rowid, c1 from t1",sArray(),"Q9c"
gs+= sTitle + $CR + JOIN$(sArray(),$CR) + STRING$(2,$CR)
slClose
END SUB
GLOBAL gs AS STRING
FUNCTION PBMAIN ()
CreateTestDatabase "Method1 Update by the month"
Method1
CreateTestDatabase "Method2 Loop through recordset"
Method2
? gs
END FUNCTION
'-----------------------------------------------------------------------------------------------------
SUB Method1 'Scan for dates in a month, then update. Perform once for each of the 12 months.
slOpen "junk.db3"
slexe "update t1 set c1 = substr(c1,-4) || '-01-' || substr(c1,-8,2) where instr(c1,'January') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-02-' || substr(c1,-8,2) where instr(c1,'February') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-03-' || substr(c1,-8,2) where instr(c1,'March') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-04-' || substr(c1,-8,2) where instr(c1,'April') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-05-' || substr(c1,-8,2) where instr(c1,'May') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-06-' || substr(c1,-8,2) where instr(c1,'June') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-07-' || substr(c1,-8,2) where instr(c1,'July') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-08-' || substr(c1,-8,2) where instr(c1,'August') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-09-' || substr(c1,-8,2) where instr(c1,'September') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-10-' || substr(c1,-8,2) where instr(c1,'October') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-11-' || substr(c1,-8,2) where instr(c1,'November') > 0"
slexe "update t1 set c1 = substr(c1,-4) || '-12-' || substr(c1,-8,2) where instr(c1,'December') > 0"
LOCAL sArray() AS STRING
slselAry "select rowid, c1 from t1",sArray(),"Q9c"
gs+=JOIN$(sArray(),$CR) + STRING$(3,$CR)
slClose
END SUB
'-----------------------------------------------------------------------------------------------------
SUB Method2 'Create recordset of dates. Update date column from first to last record.
LOCAL eleNum,num AS LONG,sNewDate,sArray() AS STRING
DIM sMonthName(1 TO 12) AS STRING
DIM sMonthNum (1 TO 12) AS STRING
ARRAY ASSIGN sMonthName() = "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"
ARRAY ASSIGN sMonthNum () = "01", "02" ,"03" ,"04" ,"05" ,"06" ,"07" ,"08" ,"09" ,"10" ,"11" ,"12"
slOpen "junk.db3"
LOCAL sArray() AS STRING
slSelAry "select rowid,c1 from t1",sArray(),"Q9c"
FOR eleNum = 1 TO UBOUND(sArray)
FOR num = 1 TO 12
IF INSTR(sArray(eleNum),sMonthName(num)) THEN
sNewDate = RIGHT$(sArray(eleNum),4) + "-" + sMonthNum(num) + "-" + MID$(sArray(eleNum),LEN(sArray(eleNum))-7,2)
slexe "update t1 set c1 = '" + sNewDate + "' where rowid =" + STR$(eleNum) 'wrong
slexe "update t1 set c1 = '" + sNewDate + "' where rowid =" + STR$(VAL(sArray(eleNum))) '3:45 PM CST 7/4/18
EXIT FOR
END IF
NEXT
NEXT
slSelAry "select rowid, c1 from t1",sArray(),"Q9c"
gs+=JOIN$(sArray(),$CR) + STRING$(3,$CR)
slClose
END SUB
'------------------------------------------------------------------------------
SUB CreateTestDatabase(sTitle AS STRING)
KILL "junk.db3"
'IF ERR = 0 THEN ? "Kill junk.db3":ERRCLEAR ELSE ? "Kill error" + STR$(ERRCLEAR)
slopen "junk.db3","C"
slexe "create table if not exists t1(c1)"
slexe "insert into t1 values('Friday, June 29, 2018')"
slexe "insert into t1 values('Saturday, June 30, 2018')"
slexe "insert into t1 values('Sunday, July 01, 2018')"
slexe "insert into t1 values('Monday, July 02, 2018')"
slexe "insert into t1 values('Tuesday, July 03, 2018')"
slexe "insert into t1 values('Unknown, December 25, 2018')"
LOCAL sArray() AS STRING
slSelAry "select rowid, c1 from t1",sArray(),"Q9c"
gs+= sTitle + $CR + JOIN$(sArray(),$CR) + STRING$(2,$CR)
slClose
END SUB