I don't knoow the best way to categorize this so when it gets cluttered, I will reorg it somehow. This section is here b/c I always forget how to do some of this stuff.

Wiki on Clarion history - http://en.wikipedia.org/wiki/Clarion_(programming_language)

QuickLinks (on this page)

• RTF Stuff
• Path Stuff
• Window stuff
• FDCB File Drop Control Box stuff
• BROWSE related stuff
• Report stuff
• MISC Procedures
• API stuff (External OS stuff)
• From the Help File - things you can never remember
• Date/Time Stuff
  

Links - external

• http://www.comformark.com.au/clariontemplates
• redirection C60EE.RED - http://www.icetips.com/showarticle.php?articleid=161
• Vista - Where to put data files under Vista - http://www.icetips.com/blog/index.php/2008/10/31/title
• Clarion links to all kinds of stuff - http://encouragersoftware.com/profile/clarlinks.html

change colors at runtime
 text
 ?yourstring{PROP:FONTCOLOR} = COLOR:RED !look at the equates.clw to get the color your want
 
 field background color (like to hilite on field error or something)
 ?yourfield{PROP:BACKGROUND} = COLOR:YELLOW

rounding - always round up
 PRE:FIELD = INT(PRE:FIELD2 / 25.4) + 1
 
Get to the history fields when changing a record

***might have to fill it in ThisWindow.init with
History::PRE:Record = PRE:Record

for regular field
history::PRE:record.FIELDNAME
 
for field in a group
history::PRE:record.PRE:GROUPLABEL.FIELDNAME
 
***lower case are need to be there, the upper case are your field/label names

force addition of record
          ThisWindow.Update
          IF ACCESS:InvoiceHeader.PrimeRecord() THEN RETURN Level:Notify.
          LOOP UNTIL ACCESS:InvoiceHeader.Insert() = Level:Benign
          END !end loop
          GlobalRequest = InsertRecord
          !REL:ReleaseNumber = 0
          UpdateInvoiceHeader()
          ThisWindow.Reset

close a file to empty it, if procedure has already opened it

Relate:FILENAME.close()
relate:FILENAME.close() !double close seemed to work
open(FILENAME,12h)
if errorcode()
  message('Error opening FILENAME - ' & errorcode() & '-' & error())
end
empty(FILENAME)
if errorcode()
  message('Error emptying FILENAME - ' & errorcode() & '-' & error())
end
Relate:FILENAME.open()

function creation inside a generated app

• create the MAP the very top of the DATA section and the function name declaration has to be all the way left
• this function sends a long and receives a long back
  •  map
    Filesexist   FUNCTION(long),long
     end
• then before the first procedure, or at the end of the mainline code (in a generated app there is only 1 line of "GlobalResponse = ThisWindow.Run()"
  • FilesExist         function(long store)
    FileQueue          QUEUE(FILE:Queue), PRE(FQ)
                       END
    LOC:ServerPath string(255)
      CODE
        LOC:ServerPath = '\\server\directory\'
        DIRECTORY(FileQueue, CLIP(LOC:ServerPath) & '*.DWN', ff_:NORMAL)
        RETURN(RECORDS(FileQueue))

RTF Stuff

https://metacpan.org/pod/The_RTF_Cookbook

RTF in Clarion - C:\clarion6\LIBSRC\rtfctl.clw

RTF removal for browse box
create a field on your screen, make it a local var, and hide it
 *loc:rtf1 is the var in example below
create a local field in your list box
 *loc:rtf2 is the var in example below

inside the BRWx.ValidateRecord procedure, after the NOT=Record:OK

!what your doing is:
! moving the field that contains the RTF controls in it to the screen var that is an rtf and hidden
! then using that rtf controls function to remove the RTF controls and put into your field that you put in your browse
! ***still havent found workaround for XPlore template when doing the output to CSV
    loc:rtf1 = FILE:MyRTFField
    display(?loc:rtf1)
    RTFControl21.GetText( loc:rtf2 ,0 ,1024 )

RTF - add some text at end of what exists

***name your RTF control class...makes it a little easier to maniplulate rather than number add ":YOURFIELD"

!check for any text in it, if not the start the rtf code

if RTFControl:YOURFIELD.props.length() = 0
PRE:YOURFIELD = '<123>\rtf -' & GLO:Initials & ' ' & format(today(),@d2) & ' ' & format(clock(),@T1) & ' - <125>'
else
PRE:YOURFIELD = PRE:YOURFIELD[1:INSTRING('}',PRE:YOURFIELD,-1,len(clip(PRE:YOURFIELD)) ) - 1] & '\par-' & GLO:Initials & ' ' & format(today(),@d2) & ' ' & format(clock(),@T1) & ' - <125>'
end

RTF - position cursor at the end of the text

***the key is to do the display first (or nothing will work)

display(?PRE:YOURFIELD)
select(?PRE:YOURFIELD,RTFControl:YOURFIELD.props.length() )

RTF on report - if you need to bold a couple words in a string
add a string to local data and make it text and rtf
populate the field on the report
in the code you cant use the { or } so use the hex of <123> or <125> respectively to assign to a string

so to bold a couple words
rtf: {\rtf Here is your {\b bolded} word.}
clarion: <123>\rtf Here is your <123>\b bolded<125> word.<125>

Example

{\rtf1
{\fonttbl
{\f0\fnil\fprq1\fcharset0 Arial;}
{\f1\fnil\fprq1\fcharset0 Georgia;}
}

this is line 1 with a bold \b word\b0  right here\par
this is line 2 with a fonted \f1\fs28 word\f0\fs17  right here\par
this is line 3 with a sized \fs28 word\fs17  right here\cf2\par
}

Path Stuff

check for backslash in path
          if instring('\',SYS:ImageDir,1,len(clip(sys:imagedir))-1) <> len(clip(sys:imagedir))
              message('          Must have \ at end!!!       ','Path Error',ICON:Exclamation)
              cycle
          end !end instring

check for path existence
          if not exists(sys:imagedir)
              message('          Directory does not exist.       ','Path Error',ICON:Exclamation)
              cycle
          end !end not exists

strip filename and path off a full path named file

        posstart# = instring('\',rtn:fullfilename,-1,len(clip(rtn:fullfilename))) + 1
        rtn:filename = sub(rtn:fullfilename,posstart#,len(clip(rtn:fullfilename)) - posstart# +1)
        rtn:pathname = sub(rtn:fullfilename,1,posstart#-1)

Directory() note - the queue will not clear unless you clear it yourself 
 
get the path only for where your program is running
    ProgramPath    = sub(command('0'),1,instring('\',command('0'),-1,len(command('0')) ) )

Hide/Disable everything - remember to enable your buttons
 DISABLE(1,LASTFIELD())
 ENABLE(?OK)
 ENABLE(?Cancel) 

ignore an accept, (dont want to fire AN accept that is on a field)
      if not 0{Prop:AcceptAll}
 ...do your normal stuff
      end !end if not 0

FDCB File Drop Control Box stuff

this is to set a combo (FDCB) based on another field being filed in
 also the FDCB is displaying a display value and the props itself

    FIL:field1 = DSP:field1
    get(FILENAME,FIL:Keybyfield1)

    Queue:FileDropCombo:2:FIL:field1    = FIL:Field1
    get(Queue:FileDropCombo:2,Queue:FileDropCombo:2:FIL:Field1)
    IF ~ERRORCODE() THEN
        ?dsp:field1{Prop:Selected} = POINTER(Queue:FileDropCombo:2)
    END
    dsp:field1 = Queue:FileDropCombo:2:FIL:Field1
    display(?dsp:field1)

FDCB drop box - clear it after selection
In your drops control "All Events"

After a FDCB selection, if there is something from the drop you need to investigate

for the accept in NewSelection embed

FDCBxx.TakeNewSelection()

FDCBxx = name of the class

BROWSE related stuff

filter a record from loading into Browse

set a variable based on browse loading
in BRWx.ResetFromView PROCEDURE
!beginning of code set your variable
    somerecordfound = 0

!in load of browse validate(BRWx.ValidateRecord PROCEDURE)

!at end of procedure set the screen var
    if somerecordfound
        ?String:MyWarning{prop:hide} = 0 
    else
        ?String:MyWarning{prop:hide} = 1
    end

set a variable during load of browse
BRWx.ValidateRecord PROCEDURE
!at end of code b/c let the browse filter take care of the not records
    If ReturnValue = Record:OK
        if somevariable
            somerecordfound = 1
        end
    End

get hi-lited record from browse queue

get(Queue:Browse,choice(?list))

look up non-related record embed point

***IMPORTANT - if you need a piece of info from the record you are deleting, like to update another file
in procedure:   (or just embeds of button)
ThisWindow.TakeAccepted PROCEDURE
OF ?Delete
      get(Queue:Browse:YOURFILE,choice(?Browse:YOURFILE))
      HoldForDelete:PRE:FIELDNAME = queue:browse:YOURFILE.PRE:FIELDNAME

Search criteria on top of the browse box to build a filter

SetSearch   routine

!SER: fields are search fields on top of the browse that your allowing input

!Match Flag Values
!Match:Simple       EQUATE(0)
!Match:Wild         EQUATE(1)
!Match:Regular      EQUATE(2)
!Match:Soundex      EQUATE(3)
!Match:NoCase       EQUATE(10H)   ! May be added to Simple,Wild and Regular


    gotfilter# = 0
    clear(hld:filter)

!Example of doing match on a string field
    if ser:frame <> ''
        if gotfilter#
              hld:filter = clip(hld:filter) & ' and'
        end
        hld:filter = ' match(sm:frame,<39>' & ser:frame & '<39>,1)'
        gotfilter# = 1
    end

!EXAMPLE of doing a numeric field with not looking at the decimals
    if ser:hp1 > 0
        if gotfilter#
              hld:filter = clip(hld:filter) & ' and'
        end
        hld:filter = clip(hld:filter) & ' sm:hp1 >= ' & clip(format(ser:hp1,@n_10.2b)) & ' and sm:hp1 < ' & clip(format(int(ser:hp1+1),@n_10b))
        gotfilter# = 1
    end

!EXAMPLE of just a straight string
    if ser:LocationPrefix <> 'AL'
        if gotfilter#
              hld:filter = clip(hld:filter) & ' and'
        end
        hld:filter = clip(hld:filter) & ' inv:locationprefix = <39>' & clip(ser:LocationPrefix) & '<39>'
        gotfilter# = 1
    else
        gotfilter# = 1
    end



    if gotfilter#
        BRW1.SetFilter(hld:filter,2)
        dsp:Filter = BRW1::View:Browse{PROP:Filter}
        display(?dsp:filter)
        BRW1.ApplyFilter()
! stop('after-' & clip(BRW1::View:Browse{PROP:Filter}))
        BRW1.ResetQueue(Reset:Queue)
        BRW1.ResetFromView()
        ?Button:Search{prop:color} = color:none
        Post(Event:ScrollTop,?Browse:1)
        select(?Browse:1)
    end
      
 exit

EIP (Edit In Place) validate field
add field as "Column Specific" (in Configure Edit In Place)...this will create embed points for that field like:
EditInPlace::PRE:FIELDNAME.xxxxxx

This is tricky. Yes, the EIP control's TakeEvent is the proper method,
but you have to put it AFTER the PARENT call; you have to PRECEED it with
an "UPDATE(SELF.Feq)"; you DON'T use the Level:Benign/Notify/Fatal but
the EditAction: equates instead; and you DON'T update the file field, but
the BRW.Q field instead! For instance, from a test program, this checks
for and disallows a blank field. This is AFTER the PARENT call.

-in EditInPlace::PRE:FIELDNAME.TakeEvent

UPDATE(SELF.Feq)
IF ReturnValue AND ReturnValue <> EditAction:Cancel THEN
    IF BRW1.Q.File1:Field1 = '' THEN
        MESSAGE('Field Cannot Be Blank','Field Error',ICON:Exclamation)
        ReturnValue = EditAction:None
    END
END

-in BRWx::EIPManager.TakeFocusLoss PROCEDURE ...before the parent call
  this is so if the user keeps hitting the insert key or some other button

  if BRWx.Q.PRE:FIELDNAME = ''
    Self.Again = true
    Self.Column = BRWx.Q.PRE:FIELDNAME
    post(event:selected)
    return
  end

-on Insert Key embed put this to disable the escape key outside the browse
    alert(esckey)

EIP (Edit in Place) update another field on the file and in the list box

***the key here is to look at the fields declared in the list/browse and get their queue:browse name

EditInPlace::PRE:FIELD.TakeAccepted PROCEDURE(BYTE Action)

after  ReturnValue = PARENT.TakeAccepted(Action)

    queue:browse:1:PRE:OTHERFIELDTOBEUPDATED = queue:browse:1:PRE:FIELD * queue:browse:1:PRE:SOMEFIELD

EIP (Edit in Place) get Detailed Interface

goto Global Properties, Classes, Browser, Template Interface -  make it Detailed

Report stuff

print another line after normal detail lines are printed from main file

-in ThisWindow.endreport put your code

filter a record (without putting it in Report Properties Record Filter)

Embeds->Local Objects->ABC objects->Process Manager->ValidateRecord(AFTER Parent Call):

if PRE:FieldName <> Whatyouwant

    ReturnValue = Record:Filtered

end

Image aspect
DisplayImage  ROUTINE
  IF ?Image1{PROP:Height} > 100
    AspectRatio$ = ?Image1{PROP:Width}/?Image1{PROP:Height}
    ?Image1{PROP:Height} = 100
    ?Image1{PROP:Width} = 100 * AspectRatio$
  END
  IF ?Image1{PROP:Width} > 100
    AspectRatio$ = ?Image1{PROP:Height}/?Image1{PROP:Width}
    ?Image1{PROP:Width} = 100
    ?Image1{PROP:Height} = 100 * AspectRatio$
  END

change a field color before printing
    SETTARGET(Report)
    if loc:MyField <= 0
        ?loc:MyField{PROP:fontcolor} = color:red
    else
        ?loc:MyField{PROP:fontcolor} = color:none
    end
    SETTARGET
  PRINT(RPT:detail)

POP a calendar date selection in a Report before running

--data section add:
calendarX calendarclass

--in ThisWindow.init after the open files
Calendar1.SelectOnClose = True
Calendar1.Ask('Select a Date',loc:reportdate)

IF Calendar1.Response = RequestCompleted THEN
loc:reportdate = Calendar1.SelectedDate
else
ReturnValue = PARENT.EndReport()
End

***notes
Loc:reportdate is a local declared variable…use whatever you want
.ask first parameter is whatever you want the title of the window to be

display filter on report

in proc "ThisWindow.OpenReport PROCEDURE "

    loc:reportfilter = Process:View{PROP:FILTER}
!!!    message('here in the report:' & clip(loc:reportfilter) )

do totals on last page only in the footer
1. add a group break on a variable that wont really change
  ***ensure you leave the footer...you can delete the group header, then copy you detail line into the detail line the group break created(use the report code)
2. data - add a local var for a counter loc:recordcount
3. in code - after the print(detail), putin in loc:recordcount += 1
4. on report - add your string prompt on footer, and hide it
5. on report - add your local var (loc:recordcount) to the footer and hide it
6. in code add the following
ThisWindow.AskPreview PROCEDURE
!***before the parent call to PARENT.AskPreview
    report$?String:NumberofRecordsPrompt{prop:hide} = 0
    report$?loc:recordcount{prop:hide} = 0

Report to CSV with GWBCSV template

 http://comformark.com/pages/clarion-templates.php
   ***NOTE - you may want to increase filename, default is 100
            -in GWBCSV.Tpl find "GWBCSVFileName  CSTRING(100)"
            -remember not to go over 218 see "ShellExecute" section
           
1. add the GWBCSVExport to your Global Extensions
2. on the Report, open the progress window
 2.1 add the Control Template "PauseButton - Pause the Process"
     ***NOTE the GWBCSVReportExport will not show until you add the Pause
 2.2 add the GWBCSVReportExport to the window (this adds the export filename to the window)
 2.3 add a close to the file b/c sometimes it still has lock
     in ThisWindow.TakeCloseEvent put:
     close(GWBCSVFile)
3. in Source
 3.1. find in ThisWindow.init where the template sets the "GWBCSVFileName" and reset if need be
  ***NOTE the default is to create the file in the directory where the program is running
 3.2. if you want to open Excel(or whatever is associated with CSV)
  ***see "ShellExecute" for adding xxxxShellExecute/xxxxGetDesktopWindow procs
  
  in Data section put:
  OPN             CSTRING(10)
  NStr            CSTRING(5)
  ShowIT          SIGNED
  NoRecordsFound Byte  
  
  in ThisReport.Close put:
  if ~NoRecordsFound !make sure the report was produced before trying to open
      OPN       = 'OPEN'
      NSTR      = ''
      Showit    = 5
      HInst# = xxxxShellExecute(xxxxGetDeskTopWindow(),OPN,GWBCSVFileName,NStr,NStr,Showit)
      IF Hinst#>32
          !noerror
      ELSE
          MESSAGE('Shell launching error:' & hinst# & '|you may need to specify a file type in Win95|Go to Explorer; View; Options; File Types|and the specify a type for ' & GWBCSVFileName,' PT')
      END
  end !end ~NoRecordFound
  
  in ThisWindow.TakeNoRecords after parent call put:
      NoRecordsFound = 1

Report print jumbled either runs together or looks funky
The print can be straight to printer or produced to a PDF.

The Report doesn't have a "default" font to fall back to.

If the DPI is anything other than "Normal" then output of fonts looks funky

To Resolve in your Report:
Add FONT('Arial',10,,,) to the actual REPORT definition

Arial is installed as default on all systems.

To Resolve in Windows(without modifying Report)
Check the DPI settings in Windows

In Windows XP:
• Go to Start > Control Panel
• Double click Display
• Select the Settings Tab and click the Advanced button
• Change the drop-down menu under DPI setting to Normal size (96 DPI) and click OK
In Windows Vista:
• Go to Start > Control Panel
• Double click Personalization
• Click Adjust font size (DPI)
• Select Default scale (96 DPI) - fit more information and click OK
In Windows 7:
• Go to Start > Control Panel
• Click Display
• Select Smaller - 100%(default) and click Apply

Font problems with the PDF generation templates
taken from:
http://www.icetips.com/blog/index.php/2009/04/21/font-problems-with-the-pdf-generation-te

The problem stems from code in the ABPRPDF.CLW file where the width of the characters is calculated.
To fix this Arnor made some changes to the files.

This involves adding a single method to the PDFGeneratorClass class in ABPRPDF.INC and ABPRPDF.CLW:

Add to class declaration in ABPRPDF.INC (I put it in at the top of the
method list, around line 195):

AdjustDPIValues   PROCEDURE(Long pValue),Long
!! AB 2009-02-19  Adjusts DPI values

In the classfile, ABPRPDF.clw, I added this:

PDFGeneratorClass.AdjustDPIValues   PROCEDURE(Long pValue)!!,Long
!! AB 2009-02-19  Adjusts DPI values
HDcScreen  UNSIGNED
IDPI       Long
R          Real
L          Long
 Code
 R = 1
 HDcScreen = GetDC(0)
 If HDcScreen <> 0
   IDPI = GetDeviceCaps(HDcScreen, 88)
   L = ReleaseDC(0, HDcScreen)
   R = IDPI/96
 End
 Return (pValue / R)

In PDFGeneratorClass.AddFontDescriptor method, you have code like this:

!SET the fonts Width
LOOP LOC:I=SELF.FontsNames.FirstChar TO SELF.FontsNames.LastChar
   LOC:CharToTest=CHR(LOC:I)
   IF GetTextExtentPoint32(LOC:DC,LOC:CharToTest, 1,LOC:CharSize)<>0 THEN
      IF LOC:Subclasing THEN
         IF SELF.FontsNames.CharWidth[LOC:I] THEN
            SELF.FontsNames.CharWidth[LOC:I] = LOC:CharSize.eW
         END
      ELSE
         SELF.FontsNames.CharWidth[LOC:I] = LOC:CharSize.eW
      END
   END
END

I changed both of the assignments to the CharWidth to:

SELF.FontsNames.CharWidth[LOC:I] = |
           SELF.AdjustDPIValues(LOC:CharSize.eW)

In PDFGeneratorClass.GetTextWidth method, you have code like this:

 IF SelectObject(LOC:DC, LOC:HFont) THEN
    RV = GetTextExtentPoint32(LOC:DC,pText, LEN(pText),LOC:CharSize)
    LOC:OLDMAPMODE=SetMapMode(LOC:DC,LOC:OLDMAPMODE)
    DeleteObject(LOC:HFont)
    RETURN LOC:CharSize.eW + (LOC:CharSize.eW/20)
 END

I changed the RETURN to adjust the value:

RETURN SELF.AdjustDPIValues(LOC:CharSize.eW + (LOC:CharSize.eW/20))

This takes care of the font sizing problem in any DPI combination that I
threw at it:)

API stuff (External OS stuff)

Clarion Open Source (cant find how to prototype something?...look here
http://www.clarionopensource.com/

WINAPI - Windows information

-take the winapi app from the C:\clarion6\Examples\RESOURCE\Winapi  ***you will probably have to compile it
-run program and pick all the stuff you want (you might also include DWORD and HANDLE from the data structures part)
-it will generate a "winapi.clw" in whatever directory you put it in
-copy the "winapi.clw" whereever your clarion looks for clw's from (in you C6EE.red file)

in your app: goto "Global Properties", "Embeds"

Include the Equates section of WINAPI.CLW in the "After Global INCLUDEs" embed point:

   INCLUDE('WINAPI.CLW','Equates')

Include the Prototypes section of WINAPI.CLW in the "Inside the Global Map" embed point:

   INCLUDE('WINAPI.CLW','Prototypes')

most equates are here
C:\clarion6\LIBSRC\winequ.clw
C:\clarion6\LIBSRC\windows.inc

you could use OR use it for ideas
C:\clarion6\LIBSRC\hndutil.clw (contains the actual procedures)
 -these are everything it uses
 --C:\clarion6\LIBSRC\hndapi.inc (defines all the mapped procedures)
 --C:\clarion6\LIBSRC\hndequ.clw (all the equates)
 --C:\clarion6\LIBSRC\hndutil.inc (is a bunch of other utils)




ShellExecute
If you doing like so below, you can switch:
RUN('cmd /c "'&CLIP(CSVFileName)&'"')

Switch to below...(it wont spawn a dos window)

bind('fieldname',fieldname)

NULL
if null(myfield) = 1     same as SQL: where myfield is null

so a compound check

if (myfield = '' or null(myfield) = 1 )

    message('My field can not be blank.')

    ?myfield{prop:background} = color:yellow

    select(?myfield)
    cycle

end

Data Types

DATE/TIME stuff - date stuff is always a pain

• Display day of week (modulus function)
  DATA 
  DisplayDayString STRING('Sunday   Monday   Tuesday  WednesdayThursday Friday   Saturday ')
  DisplayDayText   STRING(9),DIM(7),OVER(DisplayDayString)
  DayNumber short
  CODE
  AppFrame{Prop:StatusText,3} = CLIP(DisplayDayText[(TODAY()%7)+1])
  mystring = CLIP(DisplayDayText[(TODAY()%7)+1])
  DayNumber = (today()%7) + 1
  
• 6 digit numeric to real date compare
  CSTS:CSDATE = format(CSH:MDATE,@D11)
  OR
  csts:csdate = format(today()-1,@d11)
• date equivalents
  1900/01/01 = 36163
  1980/01/01 = 65382
  2008/01/01 = 75609
  2008/12/31 = 75974
• Excel to Clarion and back
  Using Formulas for Dates or Times
  To use a LONG field containing a Clarion Standard Date or a Clarion Standard Time, you can create a formula in the product you are using to access the TopSpeed files.
  Here are some examples:
  • DATE
    • Converting a Clarion date to a Microsoft Excel Date: Subtract 36161 from the Clarion date value.
    • Converting a Microsoft Excel date to a Clarion Date: Add 36161 to the Microsoft Excel date value.
  • TIME
    • Converting a Clarion time to a Microsoft Excel time: Divide the Clarion time value by 86400 (the number of seconds in one day), then divide by 100 to express as a decimal value.
    • Converting a Microsoft Excel time to a Clarion time: Multiply the Microsoft Excel time value by 0.0000001157407407407410 (1 divided by the number of seconds in a day; includes the adjustment to convert back from a percentage).
    • Exporting excel time field - change to general(so its a number it will look like .270833333)
       (cast([EXCELDECIMALTIMETOSQL] as real)/0.0000001157407407407410) + 1
• TIME - add XX minutes to clock or time field (XX is minutes you want to add/subtract)
  YOURFIELD = clock() + (XX * 6000)
  OR
  YOURNEWFIELD = TIMEFIELD + (XX * 6000)
• MISC SQL and Clarion
  
  -Clarion long - SQL date time field to long Clarion
  These are all equal...
  select datediff( dd,'18001228',DateTimeFieldName )
  select datediff( dd,'18001228','20140717' )
  select datediff( dd,'1800-12-28','2014-07-17' )
  select datediff(dd,-36163,'2014-07-17' )
  
  -time field long(in sql), view in sql
  DateAdd(ms, ClarionTimeFieldasLong * 10, '1801-01-01')                         --whole date/time field set to 1801
  DateAdd(ms, ClarionTimeFieldasLong * 10, convert(varchar(10),getdate(),120)  ) --whole date/time field set to today
  DateAdd(ms, ClarionTimeFieldasLong * 10, 0)                                    --whole date/time zero SQL date part
  DateAdd(ms, ClarionTimeFieldasLong * 10, (ClarionDateFieldasLong -36163)  )    --whole date/time field using clarion long date and clarion long time
  convert(varchar(10),   DATEADD(second, FLOOR((ClarionTimeFieldasLong - 1) / 100), 0) , 108 )            --time part only HH:MM:SS (24hour clock)
  RTRIM( RIGHT( (CAST(DATEADD(second, FLOOR((ClarionTimeFieldasLong - 1) / 100), 0) AS CHAR(24))), 13 ) ) --time part AM/PM
  
  -Time SQL datetime to Long Clarion
  

  ISNULL((CAST(DATEDIFF(ss, CONVERT(DATETIME, CONVERT(VARCHAR(10), getdate(), 120) + ' 00:00:00.000'), getdate()) AS INT) * 100) + 1, 0)

SQL and Clarion

• Clarion date to display as YYYYMMDD
  convert(datetime,([YOURCLARIONLONGDATEHERE]-36163)) AS whatevername
• SQL DateTime (from DateTime field) to long - Clarion is 8,640,000 hundreths of a second in a day
  (datepart(hh,YOURDATE)*360000) + (datepart(n,YOURDATE)*6000) + (datepart(ss,YOURDATE)*100)
• convert a concatenated fields that the date is stored in 4 spearate integer fields
  datediff(day, '18001228', convert(datetime, cast( cast((CCFIELD * 1000000 + YYFIELD * 10000 + MMFIELD * 100 + DDFIELD)as int(8)) as char(8) ) )  )
• SQL datetime field to Clarion date
  datediff(day,'18001228',SQLDATETIMEFIELD)
• SQL convert Clarion long date
  convert(datetime,([YOURCLARIONLONGDATEHERE]-36163)) AS whatevername
• SQL to convert clarion date to just date part
  

  select convert(date,convert(datetime,([clariondatefield]-36163))),* from filename

• SQL date time field to long Clarion (to get to the number)
  These are all equal...
  select datediff( dd,'18001228',DateTimeFieldName )
  select datediff( dd,'18001228','20140717' )
  select datediff( dd,'1800-12-28','2014-07-17' )
  select datediff(dd,-36163,'2014-07-17' )
• Clarion long time field to SQL, view in sql
  DateAdd(ms, ClarionTimeFieldasLong * 10, '1801-01-01')                                   --whole date/time field set to 1801
  DateAdd(ms, ClarionTimeFieldasLong * 10, convert(varchar(10),getdate(),120)  ) --whole date/time field set to today
  DateAdd(ms, ClarionTimeFieldasLong * 10, 0)                                                   --whole date/time zero SQL date part
  DateAdd(ms, ClarionTimeFieldasLong * 10, (ClarionDateFieldasLong -36163)  )    --whole date/time field using clarion long date and clarion long time
  convert(varchar(10),   DATEADD(second, FLOOR((ClarionTimeFieldasLong - 1) / 100), 0) , 108 )            --time part only HH:MM:SS (24hour clock)
  RTRIM( RIGHT( (CAST(DATEADD(second, FLOOR((ClarionTimeFieldasLong - 1) / 100), 0) AS CHAR(24))), 13 ) ) --time part AM/PM
  
  
• SQL clear the time part of a SQL defined DateTime
  dateAdd(dd,datediff(dd,0,YOURDATETIMEFIELD),0)
  ***if your convert datetime fields from a DB that uses both parts, Clarion doesnt like the time part of a DateTime field, so it needs zeroed
  ***if you have a date that is part of a Date group over and that is defined in a browse, you will probable get error "Fractional Truncation (01004)"
• SQL function (scalar) to convert Clarion date to SQL datetime field
  ***this tip taken from news.softvelocity.com/s.c.thirdparty - Alan Telford
  ***also FM3 puts ds_ConvertClarionDateTime if you need to use it for some SQL code
  
  • create a function in your DB
    

    create  function dbo.YOURDATABASENAME cladate ( @InClaDate as int )
    returns datetime
    as
    begin
        declare @SqlDate int
        declare @SqlDateTime datetime
        declare @ClaRoot datetime
        declare @MssqlRoot datetime
        set @ClaRoot = 'Dec 28, 1800'
        set @MSSQLRoot = 'Jan 01, 1900'
        set @SqlDate = @InClaDate - datediff(day,@ClaRoot,@MssqlRoot)
        if @InClaDate > 2994626 -- invalid Clarion Date
          return 1
        if @InClaDate = 0
            return -1     -- zero date and time
        else
            set @SqlDateTime = convert(datetime,@SqlDate,0)
      return(@SqlDateTime)
    end
    

    
    see your function in SQL Manager - under "YOURDATABASENAME, Programmability, Functions, Scalar"
    
  • to call
    select cladate(YOURCLARIONDATE) as YOURDATE, *
    from YOURFILENAME
• SQL function (scalar) to convert SQL date to Clarion int/long
  CREATE FUNCTION [dbo].[SQL2ClaDate] (@SQL_Date_Time DATETIME) 
  RETURNS INT AS 
  BEGIN
    RETURN ISNULL(CAST(DATEDIFF(d, '1800-12-28', @SQL_Date_Time) AS INT), 0)
  END
• SQL function (scalar) to convert Clarion time(int/long) to SQL
  ---- to use: SQL time(24hour) dbo.clatime(myclariontimeinlong,0)
  ---- to use: clock with AM/PM dbo.clatime(myclariontimeinlong,1)
  CREATE FUNCTION dbo.ClaTime (@Cla_Time INT, @simpletime int = null) 
  RETURNS CHAR(12) AS 
  BEGIN
  declare @timechar char(12)
   if @simpletime = 1
    set @timechar =  convert(varchar(10),   DATEADD(second, FLOOR((@Cla_Time - 1) / 100), 0) , 108 )
   else
    set @timechar = RTRIM( RIGHT( (CAST(DATEADD(second, FLOOR((@Cla_Time - 1) / 100), 0) AS CHAR(24))), 13 ) )
   RETURN @timechar
  END
• JDEdwards date CYYDDD(kinda julian format) to Clarion in SQL
  

  DATEDIFF(d, '1800-12-28', convert(char(10),dateadd(day,convert(int,right(@CYYDDD,3)),'01/01/'+left(right(@CYYDDD,5),2))-1,111) ) as dateClarion

  
  
  

Day of Week function

DayOfWeek     FUNCTION(xDate)
! Return short day of week name in xDate
!--------------------------------------------------------------------
  CODE
  RETURN CHOOSE(((xDate % 7) + 1),'Sunday','Monday','Tuesday','Wednesday',|
                                  'Thursday','Friday','Saturday')

You are here: Home-Computer Tips & Help-Programming-Clarion

Next Topic: SQL