View Complete Timestamp in VB

Question

Post reply

View Complete Timestamp in VB

tmherring

SSC Eights!

Points: 908
More actions
July 28, 2003 at 9:05 am

#80762

Does anyone out there know of a method for viewing the subsecond(fractional second) component of a timestamp in Visual Basic?
Thanks in advance.
July 31, 2003 at 12:00 pm

This was removed by the editor as SPAM

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Do you mean the DATETIME datatype? Or do you mean the SQL Server TIMESTAMP?

TRANSACT-SQL TIMESTAMP is NOT the same as SQL-92 TIMESTAMP.

TRANSACT-SQL TIMESTAMP is a binary value which is used to 'version-stamp' data rows. T-SQL TIMESTAMP is also unique for every row.

For DATETIME datatype, try:

CONVERT(DATETIME,GETDATE(),121)

-SQLBill

tmherring SSC Eights! Points: 908 More actions · Answer 2

Here is, more precisely, the problem.

If a Select is issued from a VB application and you do not know the column data types, those that return as a Timestamp (in this instance I am referring to the DATETIME data type) will be automatically shown in your VB output as 'mm/dd/yyyy hh:mm:ss [AM|PM]'. What I am looking for is a way, programatically, to show the complete Datetime as 'yyyy-mm-dd hh:mm:ss.fff' (for SQL Server) or 'yyyy-mm-dd-hh.mm.ss.ffffff' (for DB2 MVS).

I can get this to the point that it will show 'yyyy-mm-dd hh:mm:ss.' and 'yyyy-mm-dd-hh-mm-ss.', but that is it. I need the fractional second. If I knew in advance that the query would be accessing a Datetime/timestamp field I would use the Convert function (SQL Server) or the CHAR function (DB2). But unfortunately the application will never know prior to the recordset being returned from the database.

Thanks.

brendt hess Hall of Fame Points: 3256 More actions · Answer 3

Here is some code that will do just what you need. It is based off the storage of the datetime with the fractional value being the time:

Function GetMilliseconds(ByVal varDateTime As Variant) As Long

' The Decimal datatype can store decimal values exactly.

' Variables cannot be directly declared as Decimal, so

' create a Variant then use CDec( ) to convert to Decimal.

Dim decConversionFactor As Variant

Dim decTime As Variant

'K is used to convert a VB time unit back to seconds

'K = 86400000 milliseconds per day

decConversionFactor = CDec(86400000)

'Store the DateTime value in an exact decimal value called decTime

decTime = CDec(varDateTime)

'Make sure the date/time value is positive

decTime = Abs(decTime)

'Get rid of the date (whole number), leaving time (decimal)

decTime = decTime - Int(decTime)

'Convert the time value to milliseconds

decTime = (decTime * decConversionFactor)

'Return the milliseconds portion of the time

GetMilliseconds = decTime Mod 1000

End Function

tmherring SSC Eights! Points: 908 More actions · Answer 4

Thanks brendthess, that took care of the milliseconds for SQL Server. I tried to adapt the code to handle microseconds for DB2, but have begun to run into an accuracy problem. One of my testing timestamps(db2) looks like:

1997-07-28-14.55.36.816200

But VB is returning it as:

1997-07-28-14.55.36.816000

Close, but that whole horseshoe and handgrenade thing comes into play. Those damn picky users and their data accuracy .

Thanks again, if anyone has any additional insight, I'm all ears.

SQLBill SSC Guru Points: 51440 More actions · Answer 5

How about the SQL Server command CONVERT:

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)

That will return the date and time in the format you want. yyyy-mm-dd hh:mi:ss.mmm

-SQLBill

tmherring SSC Eights! Points: 908 More actions · Answer 6

If I knew that the user was going after a column that was a Datetime value, using convert would be perfect. But the application will not be able to determine the column data type until the recordset is returned. Thanks for the help though.