Microsoft KB Archive/186265

= HOWTO: Use the SQL Server DATEPART Function to Get Milliseconds =

Article ID: 186265

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6

-



This article was previously published under Q186265



SUMMARY
You can use the SQL Server DATEPART function to get the milliseconds of a SQL Server datetime field returned to a Visual Basic application.

The advantage of using the SQL Server DATEPART function is that it is simple to use and works with all versions of ADO, DAO, and RDO. The disadvantage of using the DATEPART function is that it is specific to SQL Server. However, other servers may have comparable functions.

Do not confuse the SQL Server DATEPART function with the Visual Basic DatePart function, which is used with the Visual Basic Date datatype.



MORE INFORMATION
The SQL Server DATEPART function returns a portion of a SQL Server datetime field.

The syntax of the SQL Server DATEPART function is:

DATEPART(portion, datetime)

where datetime is name of a SQL Server datetime field and portion is one of the following:

  Ms    for Milliseconds Yy   for Year Qq   for Quarter of the Year Mm   for Month Dy   for the Day of the Year Dd   for Day of the Month Wk   for Week Dw   for the Day of the Week Hh   for Hour Mi   for Minute Ss   for Second

Use the SQL Server DATEPART function in a Transact-SQL (T-SQL) SELECT statement. An example T-SQL statement using DATEPART follows:

SELECT Pubdate, DATEPART(Ms, Pubdate) FROM Titles

This would return Pubdate and the millisecond portion of Pubdate.

Sample Code
The following example uses the ActiveX Data Objects (ADO) 1.5 library. The same T-SQL statement would work with the DAO and RDO libraries also.

 Create a System Datasource named Pubs. The Datasource should use the SQL Server driver and the Pubs database that is included with SQL Server. Create a new Visual Basic Standard EXE Project. Form1 is created by default. Add a reference to the Microsoft ActiveX Data Objects library.  Place a CommandButton on Form1, and copy the following code into the Command1_Click event procedure:

Note You must change UID= and PWD= to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. Dim rs As New ADODB.Recordset Dim strSql As String Dim strCnn As String

strCnn = "DSN=Pubs;UID= ;PWD= ;DATABASE=Pubs" strSql = "SELECT pubdate, datepart(Ms,pubdate) FROM titles"

rs.Open strSql, strCnn, adOpenKeyset, adLockOptimistic rs.MoveFirst

Debug.Print "PubDate", "Milliseconds" Do Until rs.EOF Debug.Print rs(0), rs(1) rs.MoveNext Loop rs.Close

 Run the project. Click Command1 to execute the above code.</li> Choose View, Immediate Window from the Visual Basic Standard Toolbar to see the program's output.</li></ol>

<div class="references_section">