title: Extract Millisecond data from SQL stored procedure
body: <p>I have looked for answers on this subject and I have posted in another forum but this seems to be the font of all knowledge. I am trying to pull data from a SQL Server 2000 database using PowerShell. The Powershell script calls a stored procedure, this then extracts the data and export-csv outs it to, well a CSV. The problem is that the datetime fields in the outputted data have lost their milliseconds. This happens when PowerShell extracts the data from the database into a temporary table. Here are the two bits of code. </p>
  
  <p>PowerShell script</p>
  
  <pre><code>#VARIABLES
  $SqlQuery = "SAP_proc"
  #Connection Strings
  $Server = "server"
  $Database = "db_dab"
  #Output Files
  $OutputPath = "c:\Sapout.csv"
  #END OF VARIABLES
  
  #SQL Connection
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = "SAP_proc"
  $SqlCmd.Connection = $SqlConnection
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd
  $DataSet = New-Object System.Data.DataSet
  $DataOut = $SqlAdapter.Fill($DataSet)
  #$DataOut = $SqlAdapter.FillSchema($DataSet,[System.Data.SchemaType]::Source)
  #Data Manipulation
  $DataOut | Out-Null
  #Export Data to Hash Table
  $DataTable = $DataSet.Tables[0] 
  #Export Has table to CSV
  $DataTable | Export-CSV -Delimiter "," -Encoding Unicode -Path $OutputPath -NoTypeInformation
  $SqlConnection.Close()
  </code></pre>
  
  <p>Stored procedure</p>
  
  <pre><code>ALTER proc [dbo].[SAP_proc]
  as
  DECLARE @return_value int
  DECLARE @starttime datetime
  DECLARE @endtime datetime
  
  SET @starttime = DATEADD (dd, -30, CURRENT_TIMESTAMP)
  SET @endtime = CURRENT_TIMESTAMP
  
  EXEC    @return_value = [dbo].[sp_agent]
          @starttime
          ,@endtime 
  
  SELECT  'Return Value' = @return_value
  </code></pre>
  
  <p>Because the other stored procedure <code>SP_agent</code> is created by the software I can't edit it. Also I don't want to replicate the software defined stored procedure (with SELECT convert to varchar for datetime) in my command text string as it is a behemoth stored procedure.</p>
  
  <p>Any help would be massively useful. </p>