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.
PowerShell script
#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()
Stored procedure
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
Because the other stored procedure SP_agent 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.
Any help would be massively useful.
It's not a Powershell issue or a temp table issue. This is because your datetime column is converted to a string when you call export-csv using the default tostring method which doesn't include milliseconds. If you want milliseconds then specify it in the tostring method call:
$a = get-date
$a.ToString("d/M/yyyy hh:mm:ss.fff tt")
#To to something similar with your export-csv of a datatable you can create an expression:
$DataTable | select column1, column2, @{n=datecolumn;e={$_.datecolumn.ToString("d/M/yyyy hh:mm:ss.fff tt")}} | export-csv <rest of code>
Edited 10/17/2012 It seems like you are having trouble with this still. So here's a complete script which I've tested outputs milliseconds. You'll need to change the variable section to your environment. I hope this helps:
#VARIABLES
$SqlQuery = "select 'test' as column1, 'milliseconds' as column2, getdate() as datecolumn"
#Connection Strings
$Server = "$env:computername\sql1"
$Database = "tempdb"
#Output Files
$OutputPath = "./millisec.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 = $SqlQuery
$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
$DataTable | select column1, column2, @{n='datecolumn';e={$_.datecolumn.ToString("M/d/yyyy hh:mm:ss.fff tt")}} | export-csv $OutputPath -NoTypeInformation -force
$SqlConnection.Close()
#Another code example with explanation. Added on 10/23/2012
#VARIABLES
$SqlQuery = "select getdate() as datecolumn"
#Connection Strings
$Server = "$env:computername\sql1"
$Database = "tempdb"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = new-Object System.Data.SqlClient.SqlCommand($SqlQuery, $SqlConnection)
$data = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
#Notice NO millisecond
Write-Output $data
#See $data is an object of type System.DateTime
$data | gm
#There's a property called millisecond
#Millisecond Property int Millisecond {get;}
#Although you don't "see" millisecond's on screen it's still there
$data.Millisecond
#Powershell uses a types and format rules to define how data is display on screen. You can see this by looking at
#C:\Windows\System32\WindowsPowerShell\v1.0\types.ps1xml and searching for "DataTime". This file along with format file define how data is displayed
#When you display datatime screen it's implicitly calling
#Searching for datetime in types.ps1xml you'll find this line:
#"{0} {1}" -f $this.ToLongDateString(), $this.ToLongTimeString()
#for our example
"{0} {1}" -f $data.ToLongDateString(), $data.ToLongTimeString()
#So millisecond is still there, but if you want millisecond in your output to CSV, screen or file you'll need to call a ToString method with a date format. Here's an example:
$data.ToString("M/d/yyyy hh:mm:ss.fff tt")