HOWTO: Use the SQL Server DATEPART Function to Get Milliseconds

ID: Q186265

The information in this article applies to:

Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
ActiveX Data Objects (ADO), version 2.1 SP2

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 1.5 library.

Place a CommandButton on Form1, and copy the following code into the Command1_Click() event procedure:


      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

NOTE: You may need to modify the UID and PWD to represent your SQL Server installation ie UID=sa.

Run the project.

Click Command1 to execute the above code.

Choose View, Immediate Window from the Visual Basic Standard Toolbar to see the program's output.

REFERENCES

For more information on the SQL Server DATEPART() function, look in the SQL Server Books Online. Click Transact-SQL Reference, F, Functions, Date Functions.

Additional query words:

Keywords : kbADO200 kbDatabase kbGrpVBDB kbGrpMDAC kbADO210sp2
Version : WINDOWS:2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto