Issue with Datetime and precision

Question

Post reply

Issue with Datetime and precision

Richard Willis

SSC Veteran

Points: 232
More actions
April 21, 2008 at 4:14 am

#189576

Dear All,
Hope this is not too much of a Newbie question but I'm having a bit of an issue with datetime precision esp milliseconds.
I create a table in SQL Server Studi Express. It is a datetime and when I do a select after inserting with a getdate() function I see
testdt
-----------------------
2008-04-17 16:05:09.673
This is all fine.
My colleague opens a link to the DB using Visual Studio 2005. DB connection is using .NET framework for SQL Server. When he does a query all he can see is
testdt
-------------------
2008-04-17 16:05:09
How can I see the precision via this. I really need to be able to access the milliseconds.
Further down the line we are using a product called ASNA which is linking to an AS/400 and translates dates to timestamps (not SQL timestamps I hasten to add). More complications follow but the first question is how to see this precision in VS.
TIA . . . . . .
Rich

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

Select Convert(VarChar, Getdate(), 109) As Current_Date

Mahesh

[/font]

MH-09-AM-8694

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 2

I am not sure which format you are aiming at; you might want to give a look at the CAST AND CONVERT article in books online. If you are wanting the date/time in the first format it might look something like:

select convert(varchar, getdate(), 121) as Current_DT

/* -------- Sample Output: --------

Current_DT

------------------------------

2008-04-21 08:29:59.903

*/

Richard Willis SSC Veteran Points: 232 More actions · Answer 3

Thanks to you all for this one. Looks fine. At least the underlying data is coherent and we can then manipulate it as we want.

Thanks for the quick responses one and all

Cheers . . . . . . .

Rich

john.arnott SSChampion Points: 11882 More actions · Answer 4

Since you're interested in the values down to the millisecond level, you'll want to remember that the internal (binary) precission is to 3.33 milliseconds and is rounded to increments of .000, 003, and 007. For example, you'll never get '2008-04-22 10:29:53.855', but would see '2008-04-22 10:29:53.857'. See BOL for more examples.