SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Change Date Format  (Read 449 times)

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Change Date Format
« on: June 29, 2018, 05:27:39 AM »

Hello,

I have a table with records and a date column with the date format in this form: Thursday, April 27, 2017.
I would like to change it to 2017-04-27. Been having difficulties dealing with that date format.

Any ideas please.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #1 on: June 29, 2018, 06:58:31 AM »

Tried the substr function and getting 443 error.

Changing from "dddd',' MMMM dd yyyy" to "yyyy-MM-dd"

Code: [Select]
slSEL "select substr(Date, 15,4) & "-" & substr(Date, 7,2) & "-" & substr(Date, 12,2) from tblSalesOrder"
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #2 on: June 29, 2018, 08:12:10 AM »

Hi Mike,

I guess my second response was a bit misleading. I want the values of the Date column which are in "dddd',' MMMM dd yyyy" format to "yyyy-MM-dd" format for instance  "Friday, June 29, 2018" to "2018-06-29". No time. I guess slSEL won't do it. I used slEXE no change either.




Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #3 on: June 29, 2018, 08:47:12 AM »

Nothing changed. The Date column still contains dates with this format: "dddd',' MMMM dd yyyy". Over fourteen thousand records in there.

Code: [Select]
#COMPILE EXE
#DIM ALL
#include "SQLitening.inc"

FUNCTION PBMAIN () AS LONG
 
 slOpen ("PepperoniERPDB.db3","C")

 'SalesOrder
   slExe Build$("Create Table If Not Exists tblSalesOrder (TicketNo TEXT, Date TEXT, PricingScheme TEXT COLLATE NOCASE, AdultPrice REAL,", _
   "AdultDiscount REAL, AdultQuantity INTEGER, ChildPrice REAL, ChildDiscount REAL, ChildQuantity INTEGER, Movie TEXT, TotalAmount REAL,", _
   "PaymentMode Text, Event Text, Hall Text)")
   
   slExe "Create UNIQUE Index If Not Exists SalesOrderndx1 ON tblSalesOrder(TicketNo)"
   
   slExe "Create Index If Not Exists SalesOrderndx2 ON tblSalesOrder(PricingScheme, PaymentMode, Event)"
 
 
 slExe "Begin"
     slsel "select date(Date) from tblSalesOrder"
slExe "End"

END FUNCTION 
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #4 on: June 29, 2018, 09:29:35 AM »

Hello CJ,

The format you using its quite differently from what I am referring to:

Yours: "yyyy-MM-dd HH:MM:SS" to "yyyy-MM-dd". Date() just extract the Date part.

Mine:  "dddd',' MMMM dd yyyy" to "yyyy-MM-dd". I want change the date data to "yyyy-MM-dd"

I think this should work, but I missing the correct syntax here:

Code: [Select]
slSEL "select substr(Date, 15,4) & "-" & substr(Date, 7,2) & "-" & substr(Date, 12,2) from tblSalesOrder"
« Last Edit: June 29, 2018, 09:34:37 AM by Fredrick Ughimi »
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 679
    • View Profile
Re: Change Date Format
« Reply #5 on: June 29, 2018, 01:16:32 PM »

Why is the  10-byte date not starting at position 1?
>slSEL "select substr(Date, 15,4) & "-" & substr(Date, 7,2) & "-" & substr(Date, 12,2) from tblSalesOrder"

slSEL "select substr(Date,1,10) from tblSalesOrder"  'don't suggest using internal function names for column names
slSEL "select date(Date) from tblSalesOrder"


This is correct. 2018-06-29
What does your insert statement look like? slexe "insert into t1 values(current_timestamp)"


#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 LOCAL sArray() AS STRING
 slOpen "junk.db3","C"
 slexe "drop table if exists t1"
 slExe "Create Table If Not Exists t1 (TicketNo TEXT, DT)
 slexe "insert into t1 values('myticket',current_timestamp)"
 slselAry "select ticketno,date(DT) from t1",sArray(),"Q9c"
 ? JOIN$(sArray(),$CR)
END FUNCTION




#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 LOCAL sArray() AS STRING
 slOpen "junk.db3","C"
 slexe "drop table if exists t1"
 slExe "Create Table If Not Exists t1 (Date)
 slexe "insert into t1 values(current_timestamp)"

 slselAry "select date(Date) from t1",sArray(),"Q9c"
 ? JOIN$(sArray(),$CR) '2018-06-29

 slselAry "select substr(date,1,10) from t1",sArray(),"Q9c"
 ? JOIN$(sArray(),$CR) '2018-06-29

END FUNCTION
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #6 on: June 30, 2018, 05:21:28 PM »

Quote
What does your insert statement look like?

Code: [Select]
slExe "Begin"
            Errorcode& = slExe(slBuildInsertOrUpdate("tblSalesOrder", m_sTicketNo & $Nul & m_sDate & $Nul & m_sPricingScheme & $Nul & _
            m_sAdultPrice & $Nul & m_sAdultDiscount & $Nul & m_sAdultQuantity & $Nul & m_sChildPrice & $Nul & m_sChildDiscount & $Nul & _
            m_sChildQuantity & $Nul & m_sMovie & $Nul & m_sTotalAmount & $Nul & m_sPaymentMode  & $Nul & m_sEvent & $Nul & m_sHall),"E")
        slExe "End"
[/quote]

Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #7 on: July 01, 2018, 01:33:00 AM »

Hello CJ,

Quote
Should have said "How is the data formatted that is passed in the m_sDate variable?"
Is it in a SQLite date format so it is displayed in a SQLite format?

"dddd',' MMMM dd yyyy" i.e. Sunday, July 01, 2018.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #8 on: July 03, 2018, 03:54:03 PM »

Hello,

As a result of the long date format my Date column data looks like this:

Friday, June 29, 2018
Saturday, June 30, 2018
Sunday, July 01, 2018
Monday, July 02, 2018
Tuesday, July 03, 2018

I want to change Date column data to:

2018-06-29
2018-06-30
2018-07-01
2018-07-02
2018-07-03

Because I am finding inconsistencies in querying the table when the Date column data remain
in "dddd',' MMMM dd yyyy" format as opposed to "yyyy-MM-dd" format.

Code: [Select]
slSel "SELECT * FROM tblSalesOrder WHERE Date BETWEEN '" + sFrom + "' AND '" +  sTo + "'"   
 

Any ideas would be appreciated.   
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #9 on: July 03, 2018, 05:33:42 PM »

Quote
Fredrick, Is there a function to convert from that format?

I tried writing one but it wasn't working as expected. Besides I would also have to deal with the date data format.
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 679
    • View Profile
Re: Change Date Format - Method 1
« Reply #10 on: July 03, 2018, 06:34:44 PM »

Could be done in 1-pass, but would use the same logic.
Hope this gets you going.

GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN ()
 KILL    "junk.db3"
 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')"
 sl      "select rowid,c1 from t1"
 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"
 sl      "select rowid,c1 from t1"
 ? gs
END FUNCTION

SUB sl(sql AS STRING)
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(),"Q9"
 gs+= JOIN$(sArray(),$CR) + $CR + $CR
END SUB

Code: [Select]
GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN ()
 KILL    "junk.db3"
 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')"
 sl      "select rowid,c1 from t1"
 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"
 sl      "select rowid,c1 from t1"
 ? gs
END FUNCTION

SUB sl(sql AS STRING)
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(),"Q9"
 gs+= JOIN$(sArray(),$CR) + $CR + $CR
END SUB
« Last Edit: July 04, 2018, 05:23:40 PM by cj »
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #11 on: July 04, 2018, 04:56:34 AM »

CJ,

You are a genius!

Code: [Select]
GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN ()
 slOpen ("PepperoniERPDB.db3","C")
 sl      "select rowid,Date from tblSalesOrder"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-01-' || substr(Date,-8,2) where instr(Date,'January') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-02-' || substr(Date,-8,2) where instr(Date,'February') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-03-' || substr(Date,-8,2) where instr(Date,'March') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-04-' || substr(Date,-8,2) where instr(Date,'April') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-05-' || substr(Date,-8,2) where instr(Date,'May') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-06-' || substr(Date,-8,2) where instr(Date,'June') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-08-' || substr(Date,-8,2) where instr(Date,'August') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-09-' || substr(Date,-8,2) where instr(Date,'September') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-10-' || substr(Date,-8,2) where instr(Date,'October') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-11-' || substr(Date,-8,2) where instr(Date,'November') > 0"
 slexe   "update tblSalesOrder set Date = substr(Date,-4) || '-12-' || substr(Date,-8,2) where instr(Date,'December') > 0"
 sl      "select rowid,Date from tblSalesOrder"
 ? gs
END FUNCTION

SUB sl(sql AS STRING)
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(),"Q9"
 gs+= JOIN$(sArray(),$CR) + $CR + $CR
END SUB

This is for the keeps.

Thanks a million!
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet

cj

  • Master Geek
  • ****
  • Posts: 679
    • View Profile
Re: Change Date Format - Method 2 using recordset
« Reply #12 on: July 04, 2018, 02:01:12 PM »

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


Code: [Select]
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
« Last Edit: July 04, 2018, 05:23:01 PM by cj »
Logged
CJ

Fredrick Ughimi

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
    • Software Development Company
Re: Change Date Format
« Reply #13 on: July 05, 2018, 12:17:20 AM »

Hello CJ,

Method 2 is another beauty. Goes to show how using logic can solve problems. I wonder why PowerBASIC is not out there in the public domain like other major languages.

Thank you, CJ.
« Last Edit: July 05, 2018, 03:56:15 PM by Fredrick Ughimi »
Logged
Fredrick O. Ughimi

fughimi@yahoo.com
- Freedom lies in being bold -- Robert Frost, Poet