LiveCode Forums

Code: Select all

...
put "2016-01-06 14:20:52.000" into tDaten
put word 1 of tDaten into tDatum
set itemdel to "-"
put item 2 of tDatum & "." & item 3 of tDatum & "." item 1 of tDatum into tDeutschesDatum
...

Code: Select all

function date_in_past num_of_days
   put the date into tDate
   convert tDate to dateitems
   subtract num_of_days from item 3 of tDate
   convert tDate to system date
   
   ## I'll leave the conversion to SQL up to you :-)
   ## Should take place right here:
   
   return tDate
end date_in_past

Code: Select all

function formatDate what, theTarget, dayAdd
   --  what: Str - any date string
   --  theTarget: Str - the desired format to return, look up the second switch structure
   --  dayAdd: SigInt - days to add or subtract (negative values) 
   if theTarget is empty then put "SYSD" into theTarget  --  default
   
   --  at first: cleanup:
   repeat
      if char -1 of what = " " then  --  kill trailing spaces
         delete char -1 of what
      else exit repeat
   end repeat
   replace "," with " " in what  --  kill commata
   repeat                                           --  kill double spaces
      if "  " is in what then
         replace "  " with " " in what
      else exit repeat
   end repeat
   --  then decide what we have:
   put empty into myVar
   repeat for each char C in what
      if C is a number then put "N" after myVar  --  (Numeric)
      if C = "." then put "P" after myVar  --  (Point)
      if C = "-" then put "M" after myVar  --  (Minus)
      if C = " " then put "S" after myVar  --  (Space)
      if C = ":" then put "D" after myVar  --  (Double Dot)
      if (charToNum(C) > 64 AND charToNum(C) < 91) OR (charToNum(C) > 96 AND charToNum(C) < 123) then put "C" after myVar  --  (Char)
   end repeat
   --  myVar can be analyzed now:
   switch
      case ("N" is in myVar) AND ("P" is in myVar) AND ("C" is in myVar) AND NOT ("D" is in myVar)
         put "LNGD" into myFormat  --  13. May 2018  --  ### Long Date, SYSTEM SPECIFIC
         convert what from long system date to dateitems
         break
      case ("N" is in myVar) AND ("P" is in myVar) AND ("C" is in myVar) AND ("S" is in myVar) AND ("D" is in myVar)
         put "LNGT" into myFormat  --  13. May 2018 13:45  --  ### Long DateTime, SYSTEM SPECIFIC
         convert what from long system date and system time to dateitems
         break
      case ("N" is in myVar) AND ("P" is in myVar) AND NOT ("C" is in myVar) AND NOT ("D" is in myVar)
         put "SYSD" into myFormat  --  13.7.2018  --  ### Short Date, SYSTEM SPECIFIC
         convert what from system date to dateitems
         break
      case ("N" is in myVar) AND ("P" is in myVar) AND NOT ("C" is in myVar) AND ("S" is in myVar) AND ("D" is in myVar)
         put "SYST" into myFormat  --  13.7.2018 13:45  --  ### Short DateTime, SYSTEM SPECIFIC
         convert what from system date and system time to dateitems
         break
      case ("N" is in myVar) AND ("M" is in myVar) AND NOT ("D" is in myVar)
         put "SQLD" into myFormat  --  2018-07-13  --  SQL Date
         set itemdel to "-"
         put item 3 of what & "." & item 2 of what & "." & item 1 of what into what
         convert what from system date to dateitems
         break
      case ("N" is in myVar) AND ("M" is in myVar) AND ("S" is in myVar) AND ("D" is in myVar)
         put "SQLT" into myFormat  --  2018-07-13 13:45  --  SQL DateTime
         set itemdel to space
         put item 1 of what into myVar
         set itemdel to "-"
         put item 3 of myVar & "." & item 2 of myVar & "." & item 1 of myVar into myVar
         set itemdel to space
         put myVar into item 1 of what
         convert what from system date and system time to dateitems
         break
      default
         put empty into myFormat    --  couldn't resolve
   end switch
   if myFormat is empty then return empty  --  cannot find a date that I know :/
   
   --  what is now dateItems - maybe we must add some days?
   if dayAdd is not empty then
      set itemdel to comma
      add dayadd to item 3 of what
   end if
   
   --  formatting the output:
   switch theTarget
      case "LNGD"
         convert what from dateItems to  long system date 
         break
      case "LNGT"
         convert what from dateItems to long system date and system time
         break
      case "SYSD"
         convert what from dateItems to system date
         break
      case "SYST"
         convert what from dateItems to system date and system time
         break
      case "SSYD"  --  Short (Short System Date) - 2digit year
         convert what from dateItems to system date
         set itemdel to "."
         delete char 1 to 2 of item 3 of what
         break
      case "SSYT"  --  Short (Short System DateTime) - 2digit year
         convert what from dateItems to system date and system time
         set itemdel to "."
         delete char 1 to 2 of item 3 of what
         break
      case "SQLD"
         convert what from dateItems to dateitems
         set itemdel to comma
         put item 1 of what & "-" & format("%02s", item 2 of what) & "-" & format("%02s", item 3 of what) into what
         break
      case "SQLT"
         convert what from dateItems to dateitems
         set itemdel to comma
         put item 1 of what & "-" & item 2 of what & "-" & item 3 of what & \
               " " & item 4 of what & ":" & item 5 of what into what
         break
      default
         return what  --  shouldn't happen ...
   end switch
   return what  --  wOOOt!
end formatDate

Code: Select all

put 5 into zeitraum_stunde
put "select orders from torder where creation >= DATEADD(MINUTE,-"& zeitraum_stunde &",GETDATE()) into sql34