ODBC SQL Escaped Date, Time, TimeStamp Functions

[Michael Mattias]

' ODBCESCAPE.BAS
' ----------------------------------------------
' ----------------------------------------------------------------------
' 09.15.007
' Author Michael Mattias Racine WI
' Public Domain
' Compiler: PB/WIn 8.03 but should work with any 32 bit PB compiler.
' ----------------------------------------------------------------------
' Create character date, time and timestamp literals
'  for use in SQL statements.
' EXAMPLE:
#IF 0
  LOCAL         st AS SYSTEMTIME
  GetLocalTime  St
  SqlStmt      = "update mytable set ord_status='Z',last_update=" & SqlEscaped[Date|Time|TimeStamp](st) " _
                       & " where Order_no=1234"
#ENDIF
' -------------------------------------------------------
#COMPILE EXE
#DIM     ALL
#TOOLS   OFF

#INCLUDE "Win32API.INC"       ' 21 Feb 2005
' ODBC Escaped SystemTime cast to an ODBC date
' output:  {d'yyyy-mm-dd'}
FUNCTION SqlEscapedDate (st AS SYSTEMTIME) AS STRING

    LOCAL szDF AS ASCIIZ * 48,  szDate AS ASCIIZ * 48

    szDf          =  "'{d'''yyyy'-'MM'-'dd'''}'"   ' Weird-looking but this is what worksdatabase
    GetDateFormat    BYVAL %NULL, BYVAL %NULL ,st, szDf, szDate, SIZEOF (szDate)
    FUNCTION      = szDate
END FUNCTION
' ODBC Escaped SystemTime cast to an ODBC time
' output: {t'hh:mm:ss'}
FUNCTION SqlEscapedTime (st AS SystemTime) AS STRING
    
    LOCAL szTF AS ASCIIZ * 48, szTime AS ASCIIZ * 48
    
    szTF          = "'{t'''HH':'mm':'ss'''}'"
    GettimeFormat    BYVAL %NULL, BYVAL %NULL ,st, szTF, szTime, SIZEOF (szTime)
    FUNCTION = szTime
END FUNCTION

' ODBC Escaped SystemTime cast to an ODBC timestamp
' output: {ts'ccyy-mm-dd hh:mm:ss'}
FUNCTION SqlEscapedTimestamp (st AS SYSTEMTIME) AS STRING
    
    LOCAL szDate AS ASCIIZ * 48, szTime AS ASCIIZ * 48
    LOCAL szDF   AS ASCIIZ * 48, szTF AS ASCIIZ * 48
    
    szDF  = "'{ts'''yyyy'-'MM'-'dd' '"    ' note literal space follows dd
    GetDateFormat    BYVAL %NULL, BYVAL %NULL ,st, szDf, szDate, SIZEOF (szDate)
    
    szTF =    "HH':'mm':'ss'''}'"
    GettimeFormat   BYVAL %NULL, BYVAL %NULL ,st,  szTF, szTime, SIZEOF (szTime)
    
    FUNCTION =   szDate & szTime
END FUNCTION

' -------------------------
' DEMO PROGRAM ENTRY POINT
' -------------------------
    
FUNCTION PBMAIN () AS LONG
    
    LOCAL st AS SYSTEMTIME, W AS STRING
     
    GetLocalTime    st
 
    W = USING$ ("As Date & " & $CRLF & "As Time & " & $CRLF & "As TimeStamp &", _
                 SqlEscapedDate (st), SqlEscapedTime (st), SqlEscapedTimeStamp (st))
                 
    MSGBOX W,, "Current Local Date and Time escaped for SQL statements"
    
END FUNCTION

MCM