HOWTO: Use the SQL Server DATEPART Function to Get Milliseconds (186265)

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.

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.