Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Fredrick Ughimi]

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.

[Fredrick Ughimi]

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"


[Fredrick Ughimi]

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.

[Fredrick Ughimi]

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 

[Fredrick Ughimi]

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"


[cj]

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

[Fredrick Ughimi]

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]



[Fredrick Ughimi]

Hello CJ,

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.

[Fredrick Ughimi]

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.   

[Fredrick Ughimi]

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.

[cj]

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

[Fredrick Ughimi]

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!

[cj]

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

[Fredrick Ughimi]

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.