Post reply

Saving Date AND exact Time to data table

  • June 28, 2016 at 6:10 am

    #323652

    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

  • June 28, 2016 at 6:16 am

    #1887242

    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

  • June 28, 2016 at 6:35 am

    #1887252

    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 UserIDDataObjectOperation DateTime

    311cf0e9-1197-4655-8922-803a344824c5ballisonPDD OpenProgram2016-06-28 00:00:00.000

  • June 28, 2016 at 6:41 am

    #1887258

    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

  • June 28, 2016 at 6:47 am

    #1887261

    I will check it and see what it shows

  • June 28, 2016 at 7:29 am

    #1887284

    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

  • June 28, 2016 at 7:34 am

    #1887288

    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

  • June 28, 2016 at 7:40 am

    #1887291

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

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

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