Date format for mysql 5+

[Milesa1]

Hi there AutoIt Boffins,

I know there is a simple fix for this error but I can't fathom what it is. I need to strip leading zeros from the built-in AutoIt date macros. Specifically "@MON" and "@MDAY". These two macros generate a leading zero for months or days less than 10. MySql does not want to accept these leading zeros when I try to import the text file that made using AutoIt.

The following code works for the month "@MON" portion

$file = FileOpen("c:\sensordata_with_date_time_.txt", 1)
;Names the target file for writing
    If @MON < 10 Then $month = StringTrimLeft(@MON, 1)
;Trims month to a single digit month
    $datetime = @YEAR & $month & " " & @HOUR & @MIN & @SEC
; Generates a timestamp in a specific format
    $sediment = Random(1, 2, 1)
    $sediment1 = Random(0, 9, 1)
    $sediment2 = Random(0, 9, 1)
    $sediment3 = Random(0, 9, 1)
    $sediment4 = Random(0, 9, 1)
    $sediment5 = Random(0, 9, 1)
; Generates a random sediment value
    $sensorserial = "0001"
; Hardcodes a device serial no
    $line = $datetime &@TAB& "0" & $sediment & "." & $sediment1 & $sediment2 & $sediment3 & $sediment4 & $sediment5 &@TAB& $sensorserial & @CRLF
; Creates the string

    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open file.")
        Exit
    EndIf
; Check if file opened for writing OK

    FileWriteLine($file, $line)
; Writes the string to the file

FileClose($file)

But when I code the second "IF" statement for the day "@MDAY", the SCITE debugger generates errors.

$file = FileOpen("c:\sensordata_with_date_time_.txt", 1)
;Names the target file for writing
    If @MON < 10 Then $month = StringTrimLeft(@MON, 1)
;Trims month to a single digit month for months less than 10
    If @MDAY < 10 Then $day = StringTrimLeft(@MDAY, 1)
;Trims day to a single digit day for days less than 10
    $datetime = @YEAR & $month & $day &" " & @HOUR & @MIN & @SEC
; Generates a timestamp in a specific format
    $sediment = Random(1, 2, 1)
    $sediment1 = Random(0, 9, 1)
    $sediment2 = Random(0, 9, 1)
    $sediment3 = Random(0, 9, 1)
    $sediment4 = Random(0, 9, 1)
    $sediment5 = Random(0, 9, 1)
; Generates a random sediment value
    $sensorserial = "0001"
; Hardcodes a device serial no
    $line = $datetime &@TAB& "0" & $sediment & "." & $sediment1 & $sediment2 & $sediment3 & $sediment4 & $sediment5 &@TAB& $sensorserial & @CRLF
; Creates the string

    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open file.")
        Exit
    EndIf
; Check if file opened for writing OK

    FileWriteLine($file, $line)
; Writes the string to the file

FileClose($file)

The Debugger generates this:

>Running:(3.3.0.0):C:\AutoIt3\autoit3.exe "C:\Documents and Settings\Miles\My Documents\Velcon\Velcon autoIt scripts\DataEmulator v22forum.au3"

C:\DataEmulator v22forum.au3 (7) : ==> Variable used without being declared.:

$datetime = @YEAR & $month & $day &" "& @HOUR & @MIN & @SEC

$datetime = @YEAR & $month & ^ ERROR

->17:28:58 AutoIT3.exe ended.rc:1

+>17:29:00 AutoIt3Wrapper Finished

>Exit code: 1 Time: 2.012

My Hair is Greying!

[mlowery]

In your first example, if @MON is less than 10, does $month get assigned? Unless you've snipped that out, it doesn't, and that'll be an issue for Jan-Sep. Since your second example uses the same logic for $day, that's probably why the debugger is (correctly) reporting that the $day variable hasn't been defined.

If you strip the leading zeros and connect $month and $day with no separator, how will Jan 21 look different from Dec 1? Both will strip down to "121". According to the MySQL documentation, "MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format."

Does this not work?

$line = @YEAR & "-" & @MON & "-" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & @TAB & _
    "0" & $sediment & "." & $sediment1 & $sediment2 & $sediment3 & $sediment4 & $sediment5 &@TAB& $sensorserial & @CRLF