Access cant query by date when using getdate() in sql server

[tagteam]

i have database where i import data directly into the sql server using SSIS and i use a default for the import date using getdate()



However, when i search for the date in access it wont find it unless I remove the time after the date.

3/2/2022 10:44:37 AM - this is the way that sql server is bringing it in. If I search for 3/2/2022 it is not found

If I delete the time so it is just date - 3/2/2022 - then searching by 3/2/2022 works.

If I do a >3/1/2022 it works for both. My problem is I have a lot of queries that say [enter date] and so it would be best if I could just format the date on import to not have the time stamp.

Is that possible?

[Minty]

GetDate() returns the time and date on SQL server.

If you use

Cast(Getdate() as Date)

as the default value it should only give you a date with no time portion as the time part which access should treat as you want.

[tagteam]

Ok, I was looking at something like that but didnt know if you could put it in as the sql default. I have updated my table and will test with tomorrows import.
thanks

[June7]

Advise to not use dynamic parameter popup input because cannot validate. Reference control on form for input.

See if this helps https://stackoverflow.com/questions/...ut-time-in-sql

[tagteam]

For those interested. It did not work to put Cast(Getdate() as Date) in as the default for the table. SQL server accepted it but if you opened it backup again it was back to (getDate())
So, since I am using SSIS I just import the data into a temporary table then put in a sql execute command of Update [mytable] Set ImportDate = Cast(getdate() as Date). This updates the date in the temp table to be what I need and then I used another sql execute command to append the data to the main table.