Saving Date AND exact Time to data table

  • Brad Allison

    Hall of Fame

    Points: 3473

    I was not sure what forum to post this in, but it is SQL Server 2008 R2 we are using so I will here. I have written a program and is using the DB as a backend and I have included an activity tracking log which posts transaction details into a data table (I also have one for error tracking). In my code I use LINQ to SQL. One of the fields is ActivityDateTime and when I save the data to the SQL Table, the date is okay, but the time is 00:00:00 and I have verified through the code that this is NOT the time that is being sent. The time being sent is the actual time of the transaction. For debugging purposes, I also have it writing this data to a csv file and the time is being written there correctly.

    The field is set up in DB as datetime. Am I missing something?

    Thanks for the information

  • Gail Shaw

    SSC Guru

    Points: 1004446

    DATETIME is accurate to 3 milliseconds. It won't convert a time to midnight

    Check the LINQ make sure there's no rounding/conversion happening. Also check there's no trigger on the table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Brad Allison

    Hall of Fame

    Points: 3473

    Yep, no triggers and the datetime is being sent from the code as follows: #6/28/2016 08:33:14 AM#, but the resulting row in the database table is:

    ActivityLogID UserID DataObject Operation DateTime

    311cf0e9-1197-4655-8922-803a344824c5 ballison PDD OpenProgram 2016-06-28 00:00:00.000

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Can you use Profiler to show the exact INSERT sent to SQL Server?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Brad Allison

    Hall of Fame

    Points: 3473

    I will check it and see what it shows

  • Brad Allison

    Hall of Fame

    Points: 3473

    I checked with the profiler, but there is a LOT going on with this SQL server so what I did, and this does work for some reason, is built a quick Stored Procedure to insert the records and I am using a GETDATE at the SQL level and it works. I still would like to know though why getting data with the time from the vb code would produce 00:00:00 for the time. But at any rate, it is working via a stored proc

  • Gail Shaw

    SSC Guru

    Points: 1004446

    There's probably some implicit conversion happening somewhere in the LINQ such that the value is getting cast to the LINQ equivalent of DATE before the insert.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Brad Allison

    Hall of Fame

    Points: 3473

    thanks for the response and the help. I do appreciate it

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply