You are currently offline, waiting for your internet to reconnect

HOWTO: Use the SQL Server DATEPART Function to Get Milliseconds

This article was previously published under Q186265
This article has been archived. It is offered "as is" and will no longer be updated.
You can use the SQL Server DATEPART() function to get the milliseconds of aSQL Server datetime field returned to a Visual Basic application.

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

Do not confuse the SQL Server DATEPART() function with the Visual BasicDatePart() function, which is used with the Visual Basic Date datatype.
The SQL Server DATEPART() function returns a portion of a SQL Serverdatetime 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 ofthe 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) SELECTstatement. 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. Thesame T-SQL statement would work with the DAO and RDO libraries also.
  1. Create a System Datasource named Pubs. The Datasource should use the SQL Server driver and the Pubs database that is included with SQL Server.
  2. Create a new Visual Basic Standard EXE Project. Form1 is created by default.
  3. Add a reference to the Microsoft ActiveX Data Objects library.
  4. Place a CommandButton on Form1, and copy the following code into the Command1_Click() event procedure:

    Note You must change UID=<username> and PWD=<strong password> 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=<username>;PWD=<strong password>;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						
  5. Run the project.
  6. Click Command1 to execute the above code.
  7. Choose View, Immediate Window from the Visual Basic Standard Toolbar to see the program's output.
For more information on the SQL Server DATEPART() function, look in theSQL Server Books Online. Click Transact-SQL Reference, F, Functions,Date Functions.

Article ID: 186265 - Last Review: 12/05/2015 08:42:38 - Revision: 2.3

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

  • kbnosurvey kbarchive kbdatabase kbhowto KB186265