Returns a string in SQL date format, YYYY-MM-DD which can be used with SQL statements.
Parameters:
|
Name |
Type |
Description |
|
yymmdd |
String |
A string of t characters, representing year, month and days.
Example : 140911 will be returned as 2014-09-11
This function is used for instance, when we import data from a textfile and then insert or update date fields in our database. We must use a valid date in SQLDate format. |
Returns:
|
Type |
Value |
Description |
|
String |
SQLDate |
Returns a string in SQL date format, YYYY-MM-DD which can be used with SQL statements |
Available in:
|
WindowMain |
WindowLink |
|
YES |
NO |
Used in:
|
XojoScript Name |
GUID |
|
Import ISR payment file |
52C27AC4-24B7-4B7D-9107-6223B4E10E45 |
Hint: You can find more references to XojoScripts which make use of this function by sending the following SQL statement to the database: Select id,GUID, ScriptName,ScriptCode from im_scripts where ScriptCode Like '%GetSQLDate%'
Example XojoScript:
// Import ISR Payment file, from Scripts Mainmenu
Dim text As String = GetOpenFolderItemText
Dim linefeed As String = Chr(10)
Dim i As Integer
Dim rowtext As String
Dim sql As String
Dim Result As String = ""
Dim RefNum As String = ""
Dim DatePaid As String = ""
Dim AmountPaid As String = ""
If text <> "" Then
Dim arr(-1) As String
arr=Split(text,linefeed)
For i = 0 To Ubound(arr)
rowtext = Trim(arr(i))
If rowtext <> "" Then
If len(rowtext) = 100 Then
RefNum = Mid(rowtext,13,27)
DatePaid = GetSQLDate(Mid(rowtext,60,6))
AmountPaid = Str(Val(Mid(rowtext,40,10))/100)
sql = "INSERT INTO " + GetPrefix + "isrpayment (" _
+ "ISR_Record,isrReferenceNumber," _
+ "TransactionCode, ISR_CustomerNo," _
+ "InternalRefNo,id_from,id_to," _
+ "Amount,TransactionID,DatePayment," _
+ "DateProcessed,DateCredited," _
+ "MicrofilmNo, RejectionCode,Reserved,Fees) VALUES ('" _
+ rowtext + "','" _
+ RefNum + "','" _
+ Left(rowtext,3) + "','" _
+ Mid(rowtext,4,9) + "','" _
+ Mid(rowtext,13,7)+ "'," _
+ Str(Val(Mid(rowtext,20,9))) + "," _
+ Str(Val(Mid(rowtext,29,10))) + "," _
+ AmountPaid + ",'" _
+ Mid(rowtext,50,10) + "','" _
+ DatePaid + "','" _
+ GetSQLDate(Mid(rowtext,66,6)) + "','" _
+ GetSQLDate(Mid(rowtext,72,6)) + "','" _
+ Mid(rowtext,78,9) + "','" _
+ Mid(rowtext,87,1) + "','" _
+ Mid(rowtext,88,9) + "'," _
+ Str(Val(Mid(rowtext,97,4))/100) + ")"
Result = SQLExecute(sql)
End If // len(rowtext) = 100
End If // rowtext <> ""
Next
MsgBox("ISR Records imported!")
End If // text <> ""