Type mismatch on datetime2 datatype after migrating from Access to SQL

[ExcessionOCP]

Hi,

I have just migrated the back end of my application from an Access DB to an Azure SQL instance. The transition has mostly gone fine but I am encountering a problem with a VB function stored in the front-end that I cannot fix.

I have the following code in the function:

Dim StartYear as Int
Dim Itemqry as String
Dim itemRS as Recordset

itemqry = "Select lineitemID, purchaseprice, dateofpurchase from lineitems WHERE ExcludeFromOH = False"


Set itemsrs = dbs.OpenRecordset(itemqry, dbOpenDynaset, dbSeeChanges)


itemsrs.MoveLast
totalcount = itemsrs.RecordCount
itemsrs.MoveFirst




With itemsrs
  
    Do Until .EOF


    StartYear = Year(itemsrs.Fields(2))
    Do some more stuff

End with

The code is failing with a type mismatch error on the

StartYear = Year(itemsrs.Fields(2))

line, where I take the year component of the "DateofPurchase" column in the LineItems table and store it in the StartYear variable for use later in the function.

Doing some debugging, I have noticed that the datatype for this column in the SQL database has been set to "datetime2" by the migration tool, whereas it is just "date/time" in the Access DB. I have dumped the value of itemsrs.fields(2) into a debug window in both systems and in the Access DB it is returning, for example:

15/12/2022

Whereas in the SQL DB it is returning:

15/12/2022 00:00:00.000000

Which is where I suspect the problem is.

Does anyone have any suggestions for how I overcome this? Is there a different way I should be using to extract the year component from the date field? Or do I somehow need to update all of the values in the SQL DB column to remove the trailing zeros?

Hoping someone can help

[Welshgasman]

Change the field to datetime?

[Micron]

Year function should work with anything that resembles a date, albeit calculations should be in US date format.
Or take the Left 10 of the field?
Or wrap the value in date delimiters?

[ExcessionOCP]

Thanks for the reply. I'd already tried that, but the result was still the same (both in terms of what is returned in debug.print, and the type mismatch).

[moke123]

datetime2 stores the data differently than datetime.

Unless you need the precision of datetime2, I'd stick with datetime.

[ExcessionOCP]

Thanks for the reply! I've tried switching it to datetime and also to just date but the trailing zeros are still present when I inspect the recordset entry, even though they aren't shown in the table - I'm really stumped!

[Micron]

So using Left is one thing you tried that didn't work?

[moke123]

Did you update your ODBC Driver?

Enabled Date/time extended in access?

[madpiet]

itemqry = "Select lineitemID, purchaseprice, YEAR(dateofpurchase) AS PurchaseYear from lineitems WHERE ExcludeFromOH = False"

Since YEAR() returns an integer, no more date problem.

[ExcessionOCP]

itemqry = "Select lineitemID, purchaseprice, YEAR(dateofpurchase) AS PurchaseYear from lineitems WHERE ExcludeFromOH = False"

Since YEAR() returns an integer, no more date problem.

Of course! Why didn't I think of that! Thanks very much, I'll give that a go and report back.

[ExcessionOCP]

Did you update your ODBC Driver?

Enabled Date/time extended in access?

I've enabled the extended Date/Time Access option, the ODBC driver is a good point though. I'll check to see how up-to-date it is then I'll try implementing madpiet's idea (don't want to change two things at once and all that!)

[Minty]

Avoid using the data type DateTime2 with Access like the plague.
There are a lot of weird problems with it even with the most up to date ODBC drivers, and the latest version of Access.

We have even seen things like a default value (Set in the SQL table) of Getdate() failing on precision issues.

[ExcessionOCP]

This turned out to be the answer! Thanks madpiet!