« Previous Next »

Thread: Inserting higher resolution dates into SQL Server 2008 datetime2 type

Last post 05-14-2009 7:35 PM by MikeGale. 1 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (2 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 05-14-2009, 2:35 AM

    • MikeGale
    • Not Ranked
    • Joined on 07-31-2002, 12:11 PM
    • Posts 4

    Inserting higher resolution dates into SQL Server 2008 datetime2 type

    Reply Contact

    I have some time data in logs with a sub-second time resolution.

    I can get this safely into log parser using the iTsFormat setting.

    When I put that into SQL Server 2008 I have (so far) gotten the times in seconds (loss of resolution).  The target field is datetime2 (not datetime).

    How do I preserve the resolution when inserting the data?

    Mike Gale
    Tags:

  • 05-14-2009, 7:35 PM In reply to

    • MikeGale
    • Not Ranked
    • Joined on 07-31-2002, 12:11 PM
    • Posts 4

    Re: Inserting higher resolution dates into SQL Server 2008 datetime2 type

    Reply Contact

    Here's details of my current fix for this:

    I have added a text field with the datetime string in it.  (For conversion in the database.)

    The command line code looks like:

    MikeGale:

    ... -iTsFormat:"yyyy-MM-dd hh:mm:ss.ll.nn" -o:SQL ... "SELECT sTime, TO_STRING(sTime)..."

    (I was expecting it to need the timestamp version of TO_STRING, but that didn't work!)

    I then dug deeper and found that the documentation is unaware of timestamp2, though it does know about  datetime, smalldatetime, date and time.

    I guess one way to handle this in future versions would be to allow -oTsFormat:"yyyy-MM-dd hh:mm:ss.ll.nn" with a SQL Server output and intelligently handle the type issues, or just add timestamp2 to the recognised types and just do it.

     

    Mike Gale
Page 1 of 1 (2 items)
Microsoft Communities