X_datetime() - convert database datetime stamps

[blueznl]

While retrieving information from different monitoring systems (using either SQL or exported .CSV files) I encountered all sorts of timedate formats. Even the different SQL implementations may return different formats.

I quickly cooked up the code below to reformat all to something that, thus far, seems to be accepted as input by most of those systems

Nothing special, but it might save someone else some time.

Code: Select all

Procedure.s x_datetime(string.s, seperator.s="-")                    ; turn any timestamp into a standard format
  Protected date.s, day.i, month.i, year.i, time.s, hour.i, minute.i, second.i, x.s
  ;
  ; *** reformat datetime stamps to dd-mm-yy hh:mm:ss
  ;
  ; in:     string.s    - datetime to convert
  ;         seperator.s - seperator character to use in output
  ; retval:             - reformatted datetime
  ;
  ; (exports from) sql and other dabases may return datetime stamps in various formats
  ; this procedure always turns them into DD-MM-YYYY HH:MM:SS
  ;
  day = 0
  year = 0
  month = 0
  hour = 0
  minute = 0
  second = 0
  ;
  If seperator <> "-" And seperator <> "/" And seperator <> "."
    seperator = "-"
  EndIf
  ;
  If string > ""
    ;
    ; reformat date
    ;
    ReplaceString(string,".",seperator,#PB_String_InPlace)
    ReplaceString(string,"/",seperator,#PB_String_InPlace)
    ReplaceString(string,"-",seperator,#PB_String_InPlace)
    ;
  EndIf
  ;
  ; process date
  ;
  date = StringField(string,1," ")
  If date > ""
    day = Val(StringField(date,1,seperator))
    month = Val(StringField(date,2,seperator))
    x = StringField(date,3,seperator)
    If x = ""
      year = Year(Date())
    ElseIf Len(x) = 2
      year = Val(x)
      If year >= 70
        year = 1900+year
      Else
        year = 2000+year
      EndIf
    Else
      year = Val(x)
    EndIf
    ;
    ; process time
    ;
    time = StringField(string,2," ")
    If time > ""
      hour = Val(StringField(time,1,":"))
      minute = Val(StringField(time,2,":"))
      second = Val(StringField(time,3,":"))
    EndIf
  EndIf
  ;
  If day >= 1 And day <= 31 And month >= 1 And month <= 12 And year >= 0 And year <= 9999
    x = Right("0"+Str(day),2)+seperator + Right("0"+Str(month),2)+seperator + Right("000"+Str(year),4)+" "
    x = x + Right("0"+Str(hour),2)+":" + Right("0"+Str(minute),2)+":" + Right("0"+Str(second),2)
    ProcedureReturn x                         
  Else
    ProcedureReturn "**"+seperator+"**"+seperator+"**** **:**:**"
  EndIf
  ;
EndProcedure

Procedure.s x_compactdatetime(string.s)
  ;
  ; *** convert datetime stamp to compact sortable string
  ;
  ; input:  string - string in dd-mm-yyyy hh:mm:ss format or similar
  ; retval:        - yyyymmddhhmmss
  ;
  ; see also:      - x_datetime()
  ;
  string = x_datetime(string)
  ProcedureReturn Mid(string,7,4)+Mid(string,4,2)+Mid(string,1,2)+Mid(string,12,2)+Mid(string,15,2)+Mid(string,18,2)
EndProcedure