Subject Re: date settings
From Gaetano <gaetanodd@hotmail.com>
Date Sat, 2 Jan 2021 07:23:10 +1000
Newsgroups dbase.getting-started
Attachment(s) MG_dates.wfm

On 02/01/2021 06:47, Ken Mayer wrote:
> On 1/1/2021 12:20 PM, Gaetano wrote:
 >> On 02/01/2021 04:04, Ken Mayer wrote:
 >>
 >> What Mustansir is seeing is typical of YMD string interpretation in
>> dBase. When you recently asked a question about dates in NG.Internet,
>> I did a couple tests and I had a similar output with year = 2007 I
>> think. Now the incorrect years are 2021 or 2183 (there isn't even a
>> "3" or an "8" in the string, where does 2183 come from?):
 >
> Okay, well perhaps this should be (if it hasn't been) reported in the
> bug-reports newsgroup. I don't know what else to say.
 >
> Ken
 >
Already done, by a few people, including myself, but the workarounds are
easy, so the chances of this being fixed are low.

Attaching a simple form to demonstrate the behaviour, the old setting is
stored and restored:

function form_onOpen()
        clear
       this.entryfield1.value=date()                
                a=this.entryfield1.value
                cOld = set('date')
                set date to YMD
                b=this.entryfield1.value
                set date to &cOld
                msgbox("Value before changing to YMD= "+a+CHR(10)+;
                                                "Value after changing to YMD= "+b)
       return


Note to Mustansir, if the purpose of changing the date format to YMD is
to load in SQL, I would recommend to leave your local settings as they
are and write a small function to handle the dates.

I have put together this code to generate an SQL and data object
compatible output:

function DTTOS(dDT)
        local dDate,tTime,cRet
        //must supply a dateTime value, e.g. from Timestamp field or {literal
timestamp} or datetime()
        
        dDate =
substr(DTOS(dDT),1,4)+"-"+substr(DTOS(dDT),5,2)+"-"+substr(DTOS(dDT),7,2)
        tTime = TTOC(dDT)
        cRet = dDate+" "+tTime
        return cRet

Because DTOS() will produce the same text string based on a datetime
input, no matter your local settings, DTTOS() will always produce the
same format which you can use both in dBase and in SQL.

If you only need the date part of it, this will do the trick:


function DTOSQL(dDT)
        local dDate,cRet
        //must supply a date value, e.g. {literal date}, CTOD(<date>), or date()
        
        cRet =
substr(DTOS(dDT),1,4)+"-"+substr(DTOS(dDT),5,2)+"-"+substr(DTOS(dDT),7,2)
        
        return cRet



** END HEADER -- do not remove this line
//
// Generated on 01/02/2021
//
parameter bModal
local f
f = new MG_datesForm()
if (bModal)
   f.mdi = false // ensure not MDI
   f.readModal()
else
   f.open()
endif

class MG_datesForm of FORM
   with (this)
      onOpen = class::FORM_ONOPEN
      height = 16.0
      left = 117.0
      top = 0.0
      width = 70.4286
      text = ""
   endwith

   this.ENTRYFIELD1 = new ENTRYFIELD(this)
   with (this.ENTRYFIELD1)
      height = 1.0
      left = 28.0
      top = 3.0
      width = 14.7143
      value = ""
   endwith


    
   function form_onOpen()
        clear
      this.entryfield1.value=date()                
                a=this.entryfield1.value
                cOld = set('date')
                set date to YMD
                b=this.entryfield1.value
                set date to &cOld
                msgbox("type of entryfield= "+type("this.entryfield1.value")+CHR(10)+;
                                                "Value before changing to YMD= "+a+CHR(10)+;
                                                "Value after changing to YMD= "+b)
      return

endclass