Consulting

Results 1 to 5 of 5

Thread: Date/Time Extended field in SQL server table

  1. #1
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location

    Date/Time Extended field in SQL server table

    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?

  2. #2
    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;

  3. #3
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location
    Quote Originally Posted by arnelgp View Post
    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...."

  4. #4
    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;

  5. #5
    VBAX Regular
    Joined
    Jul 2016
    Posts
    22
    Location
    Thank you so much for your help. But i found a way around this. There is a setting to deactivate:

    Pic attachedScreenshot 2024-06-20 170108.jpg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •