ADO SQL - time incorrectly adds date in AHK (COM bug?) Topic is solved

[hobboy]

I'm doing SQL with ADO using COM. When I retrieve a 'time' from a recordset the value I read is incorrect. For example, my query in AHK will return the time '15/08/2017 3:10:00 PM' instead of '3:10:00 PM'. Note that the added date is simply the today's date, not related to the any date field associated with the query. Other programs simply return the time and no date, as expected.

The field type for the returned value is 134 (adDBTime = A time value (hhmmss) according to https://www.w3schools.com/asp/prop_field_type.asp). So the value is expected to be simply a time, but when I read the value it comes out with today's date.

This only seems to happen in AHK - when I try the same query in Excel with ADO it's correct. When I read the value from the field (e.g. fields.Item( A_Index - 1 ).Value) it is incorrect, so I don't think it's my code (might be wrong). I'm wondering whether AHK does some kind of conversion to produce the result I see, but it's unexpected. Could this be a bug?

I'm running v1.1.26.00 32bit unicode on windows 7.

I've included some example code (however it can't run).

Code: Select all

_conn_str := "some connection string"

conn := ComObjCreate( "ADODB.Connection" )

conn.Open( _conn_str ) ; open the connection.

sql_query =
(
some query
)

record := conn.execute( sql_query ) ; execute sql statement

table := recordset_to_dict(record)

conn.Close()

ExitApp

recordset_to_dict(record)
{
	; pointer starts on the first row (fields as columns)
	fields := record.Fields

	MyTable := []

	cols := fields.Count
	headings := []
	headings.SetCapacity(cols)
	; get the headin from only the first row (for speed).
	Loop % cols
	{
		ColName := fields.Item(A_Index-1).Name
		headings[A_Index] := ColName
	}

	; While the record pointer is not at the end of the recordset...
	While !record.EOF
	{
		; reinitialise to avoid pointer problems
		entry := {}
		Loop % cols
		{
			ColName := headings[A_Index]
			entry[ColName] := fields.Item( A_Index - 1 ).Value
		}
		MyTable.Push(entry)

		record.MoveNext() ; move the record pointer to the next row of values
	}
	return MyTable
}

[Gio]

Hello hobboy.

This is a common issue when connecting to databases. It is not an AutoHotkey fault, the datetime format is regularly messed by many layers of software in a connection unless you explicitly specify how you whant the date and time to be returned in the query. I have even seen it vary between two computers running the same code. You have to force the date time format if you whant a consistent result.

You can force a datetime format in a query like this:

Code: Select all

SELECT CONVERT(VARCHAR, GETDATE(), 108)

For other formats see https://docs.microsoft.com/en-us/sql/t- ... ansact-sql

And if you whant, you can also use AutoHotkeys string functions/commands to output custom datetime formats from a returned result.

On another note, i also suggest you use VxEs function for SQL connections, since it has a more robust implementation:
https://autohotkey.com/boards/viewtopic ... lit=adosql

Best wishes.

[hobboy]

Thanks, that sorted it out! I'll be careful about my dates and times in the future.

I'll look into ADOSQL as well.