Results 1 to 9 of 9

Thread: MSSQL DateTime DateTime2

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009

    Default MSSQL DateTime DateTime2


    Messing around with DateTime and DateTime2 in MSSQL
    Here a number of Dump which are for me at the moment hard to explain.

    Database Definition

    Click image for larger version. 

Name:	DateTime_DateTime2_File_Definition.png 
Views:	37 
Size:	8.3 KB 
ID:	13283

    Filling a record.

          Open Test
          Clear Test
          Move 1000 to test.Test_ID
          Move (CurrentDateTime ()) to Test.dtTest
          Move (CurrentDateTime ()) to Test.dtTest2
          SaveRecord Test
    Result in the debugger

    Click image for larger version. 

Name:	DateTime_DateTime2_Debugger.png 
Views:	29 
Size:	5.1 KB 
ID:	13284

    In the dbExplorer

    Click image for larger version. 

Name:	DateTime_DateTime2_dbExplorer.png 
Views:	23 
Size:	6.8 KB 
ID:	13285

    Result in SQL Management studio

    Click image for larger version. 

Name:	DateTime_DateTime2_Management Studio.png 
Views:	22 
Size:	13.2 KB 
ID:	13286

    According to microsoft DateTime2 has a better accuracy then DateTime

    DateTime Accuracy Rounded to increments of .000, .003, or .007 seconds
    DateTime2 Precision, scale 0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.

    So using following SQL script

      Insert into [test] (Test_ID, dtTest, dtTest2)
      Values ('1001', GetDate(), SYSDATETIME())
    No proper result still no milli seconds

    But looking in the SQL management studio the field named dtTest2 create as DateTime2 in the studio is created as datetime2(0) so no precision at all.

    So I have to create the datatime2 with a size of 23.7, scanned the help. No information about.

    Testing again with the same SQL script I get all the digits. Management studio shows the correct 7 digits. Reading the record and field with 7 digit into DateTime variable the Max is 3 digits. Looks like the DateTime in dataflex is not more then with a 3 precision.

    Maybe something that can get attention in 20.0

    Last edited by phvwijk; 3-Dec-2019 at 02:15 PM.
    Peter H. van Wijk
    X-Organize Consultancy N.V.

Posting Permissions

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