Date/Time Extended field in SQL server table

[ry94080]

Hello,

I'm linking to table in SQL server and a date time field is displaying with a decimal at the end like this, #1/1/2024 08:08:00.0#. When i look at the design of the table, it says that field is setup as Date/Time Extended?

When i write a query against the field. It sees it as text when i try to do a >=Date()-1. When i try to do a cDate on the field, it returns an #Error.

Any ideas how to handle this field?

[arnelgp]

you could try to create a Public function to a Module to convert it to Access Date and Time:

' arnelgpPublic Function MSSQLDateTime2ToDate(ByVal e As Variant) As Variant
Dim i As Integer
If IsNull(e) Then
MSSQLDateTime2ToDate = Null
Exit Function
End If
i = InStr(1, e, ".")
If i <> 0 Then
e = Left$(e, i - 1)
End If
MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e) End Function

then on your query, you could use the function to create another Expression:

Select * From yourTableName Where DateVaue(MSSQLDateTime2ToDate([theDateFieldName])) >= Date()-1;

[ry94080]

you could try to create a Public function to a Module to convert it to Access Date and Time:

' arnelgpPublic Function MSSQLDateTime2ToDate(ByVal e As Variant) As Variant
Dim i As Integer
If IsNull(e) Then
MSSQLDateTime2ToDate = Null
Exit Function
End If
i = InStr(1, e, ".")
If i <> 0 Then
e = Left$(e, i - 1)
End If
MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e) End Function

then on your query, you could use the function to create another Expression:

Select * From yourTableName Where DateVaue(MSSQLDateTime2ToDate([theDateFieldName])) >= Date()-1;

I tried this and MSAccess is giving me the error: "This expression is typed incorrectly, or it is too complex to be evaluated...."

[arnelgp]

the functiion is like this (correction):


' arnelgpPublic
Function MSSQLDateTime2ToDate(ByVal e As Variant) As Variant
Dim i As Integer
If IsNull(e) Then
MSSQLDateTime2ToDate = Null
Exit Function
End If
i = InStr(1, e, ".")
If i <> 0 Then
e = Left$(e, i - 1)
End If
MSSQLDateTime2ToDate = DateValue(e) + TimeValue(e)
End Function


now create first a query(query1):

SELECT MSSQLDateTime2ToDate([theSQLDateFieldHere]) AS MSADate, *
FROM linkedTableNameHere;


then create the needed query (query2, your final query):

SELECT * FROM query1 Where [MSADate] >= Date() + 1;

[ry94080]

Thank you so much for your help. But i found a way around this. There is a setting to deactivate:

Pic attached31657