Solved Convert to SQL-format date

[Kayleigh]

Hi I have a DB where FE is MS Access and BE is SQL Server.
I am in the process of checking all queries are functional due to the change from Jet to SQL. I have one update query which is constantly coming up with data conversion error. The field is in the format of : 2021-08-10 00:00:00
The calculation in the 'Update to' statement evaluates to a date in the format of : 25/10/2021
How do I amend this so it will be in format of field?

 

[theDBguy]

Can you post the SET part of your query involving the date field?

 

[Kayleigh]

UPDATE tblOrders INNER JOIN lkptblOrderStatus ON tblOrders.fldOStatusID = lkptblOrderStatus.fldOrderStatusID SET tblOrders.fldOAnticipatedPayDate = IIf(Weekday(Date())=7 Or Weekday(Date())=1,(Date()+8-Weekday(Date(),2)),Date()-(DatePart("w",Date(),2,1)-1))
WHERE (((lkptblOrderStatus.fldOSSort)<100) AND ((IIf(Weekday([fldOAnticipatedPayDate])=6,[fldOAnticipatedPayDate],([fldOAnticipatedPayDate]+8-Weekday([fldOAnticipatedPayDate],6))))<Date()));

 

[theDBguy]

UPDATE tblOrders INNER JOIN lkptblOrderStatus ON tblOrders.fldOStatusID = lkptblOrderStatus.fldOrderStatusID SET tblOrders.fldOAnticipatedPayDate = IIf(Weekday(Date())=7 Or Weekday(Date())=1,(Date()+8-Weekday(Date(),2)),Date()-(DatePart("w",Date(),2,1)-1))
WHERE (((lkptblOrderStatus.fldOSSort)<100) AND ((IIf(Weekday([fldOAnticipatedPayDate])=6,[fldOAnticipatedPayDate],([fldOAnticipatedPayDate]+8-Weekday([fldOAnticipatedPayDate],6))))<Date()));

Thanks. Assuming the field in question was the with PayDate in it, what data type did Access assign to it if you got to design view of the table in Access?

 

[Minty]

That looks like it is a DateTime2 from SQL server in an older version of Access to me.

What ODBC driver are you using, that will probably need updating.

 

[Kayleigh]

Is this ODBC driver you mean?

 

[Kayleigh]

Thanks. Assuming the field in question was the with PayDate in it, what data type did Access assign to it if you got to design view of the table in Access?

Also I've checked the design view but it didn't offer any insight - just date/time like other fields.

 

[theDBguy]

Also I've checked the design view but it didn't offer any insight - just date/time like other fields.

That's fine. I would suggest adding a test/dummy record and try to update just that one. For example:

UPDATE TableName SET DateField=Date() WHERE ID=xxx

This avoids the long IIf() expression you're trying to use, just to eliminate that part for now.

 

[Kayleigh]

I've tested the IIf as a field on its own and it will evaluate correctly - only displaying at the SQL date format.

 

[theDBguy]

I've tested the IIf as a field on its own and it will evaluate correctly - only displaying at the SQL date format.

But in my mind, the problem wasn't if the IIf() works or not. The error was "conversion failure," so I was thinking it's the part when you're assigning a value to the date field. That's why I suggested to assign a simple Date() to the field as a test.

 

[Kayleigh]

I understand. The DB I'm working on is for test purposes so it is okay to overwrite the data.

Not so familiar with the way SQL server works. I've added new dummy column to table but its not updating in my FE - even after I've closed and re-opened both. What do you advise?

 

[theDBguy]

I understand. The DB I'm working on is for test purposes so it is okay to overwrite the data.

Not so familiar with the way SQL server works. I've added new dummy column to table but its not updating in my FE - even after I've closed and re-opened both. What do you advise?

Try refreshing the link. Failing that, you can delete the linked table and relink it again. However, I didn't recommend adding a new column. I simply wanted you to add a "new record" to easily identify a single record to update and test.

 

[Kayleigh]

Okay I've done as you specified - same field only one record to change and this is error that restricts query execution.

 

[Kayleigh]

I've given this a try instead of date():
Format(Date(),"mm/dd/yyyy hh:nn AM/PM")

and it has worked successfully! So I will try with the calculated field now...

 

[theDBguy]

I've given this a try instead of date():
Format(Date(),"mm/dd/yyyy hh:nn AM/PM")

and it has worked successfully! So I will try with the calculated field now...

Keep us updated...

 

[Kayleigh]

Seems to be working with to update with Iif statement when I format as above.

Thanks for pointing me in the right direction

 

[theDBguy]

Seems to be working with to update with Iif statement when I format as above.

Thanks for pointing me in the right direction

Hi. Glad to hear you're making good progress. Good luck!

 

[Minty]

Is this ODBC driver you mean?

Yes - You would need to use the V17 one not the native client. It doesn't support the datetime2 data type that is the default in later SQL server versions.

In the SQL server what data type is that date field set to?