[SOLVED] Datetime from SQL Stored Procedure

by wbbigdave at 2012-10-05 08:23:51

Hello,

I am trying to get powershell to drop the results of a Stored Procedure to a CSV file for exporting to another system which doesn’t play nice with SQL in the first place.

After learning Powershell enough to write a semi decent script I hit a problem that in the SP the datetime fields contain millisecond data which is needed in the external application, however Powershell does not pull this data from the Storedprocedure output.

This isn’t anything to do with the export-CSV cmdlet as if i simply file-out the data I can’t see a millisecond output.

Can anyone help as my PowerShellFu is not good enough.
by surveyor at 2012-10-05 11:56:26
Without any knowledge what you have tried yet it’s very hard to say what you need. Can you show the PowerShell-Script which makes the problem?

.NET DateTime Millisecond
by RichardSiddaway at 2012-10-06 02:05:16
PowerShell can work with milliseconds

You need to break the data down
PS> $date = Get-Date -Year 2012 -Month 10 -Day 10 -Hour 4 -Minute 31 -Second 16 -Millisecond 367
PS> $date

10 October 2012 04:31:16

This also works

PS> [datetime]‘10/10/2012 4:32:16.367’

10 October 2012 04:32:16
by wbbigdave at 2012-10-06 02:29:09
Ach the code is on my work laptop but basically I am creating a sql connection and pulling the data with the Sql adapter into a dataset. This is then export-null and a table is populated. If I look st this table in PowerShell or export-csv it, then no milliseconds.

How would I break the data down in the table? I tried formatting it and i can’t remember exactly how, something to do with calling the start_time field against the date time I got under a specific format but when I export-csv’ d it I only got that column.
by wbbigdave at 2012-10-08 00:13:05
This is the code i have written minus the variables.

$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

#Data Manipulation
$DataSet = New-Object System.Data.DataSet
$DataOut = $SqlAdapter.Fill($DataSet)
$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()
by wbbigdave at 2012-10-12 02:53:26
Does anyone have any ideas. I am running out of time and patience with this problem now. I know i can get-date with milliseconds, to an extent, but I really need the data pulled using this script to come in with the millisecond data.
by Klaas at 2012-10-12 03:08:35
I tried your code on an existing table with datetime. When I ‘SELECT datetimefield FROM Mytable’ there are no milliseconds. When I ‘SELECT CONVERT(VARCHAR(30),datetimefield,113) FROM Mytable’ the milliseconds are back.
So maybe adapting your query fixes everything. Of course it depends on your SQL Version and the format you want, there’s plenty of choice.
by wbbigdave at 2012-10-12 03:16:44
so if i alter my stored procedure to convert the datetime to a varchar you think it might work when i pull from the DB? Ill give it a whizz and try it, it;s SQL 2000 btw. Not my choice stupid pre installed application version.
by wbbigdave at 2012-10-12 03:23:48
hmm looking at my SP again I don’t think I can edit the way it SELECTs the data. Here is the SP, it calls a pre defined SP which I definately cannot edit

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



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_call_]
@starttime
,@endtime

SELECT ‘Return Value’ = @return_value
by Klaas at 2012-10-12 04:16:03
In all my tests Powershell writes to the csv exactly what is in the dataset. If there are ms in the data, then there are ms in the csv or in the shell or in a HTML, whatever the choice.
I’m convinced the solution is on the SQL side, and your PSscript is fine.
Can you run a test with a SELECT to a view or table column with ms in it instead of the stored procedure in your $query?
by wbbigdave at 2012-10-17 05:43:00
I have run that Stored Procedure before now and when I save to a file from the results grid I get the results I need. I think the problem is in the export-csv output of the DataTable, at the moment I am trying to do a select on the Columns in the filled table. I can’t get my syntax right though :confused:


$DataTable | select Start_Time, End_Time, @{n=datecolumn;e={$_.Sdatecolumn.ToString(“yyyy/mm/dd hh:mm:ss.fff tt”)}} | Export-CSV -Delimiter “,” -Encoding Unicode -Path $OutputPath -NoTypeInformation


Not sure what the datecolumn bit should be in the array.
by Klaas at 2012-10-17 06:50:37
So if you replace ‘Export-csv’ with ‘out-gridview’ you do have the milliseconds?
by wbbigdave at 2012-10-17 08:22:51
No,

I am currently returning all of my data tables after I have done the export. I am trying different things with fill.schema but im not sure if this is working, also trying different things with the sql adapter.

If you have any advice on this then I would be incredibly grateful.
by wbbigdave at 2012-10-18 02:19:45
Ok different tact here, trying different options for loading the DataSet, tried all types of fill load options etc, the one I am currently trying is setting the DataSet.REmotingFormat but I cna’t set it to Binary, it keeps saying I have to use the Enumeration Values as “Xml, Binary” this is the code im trying to use

$DataSet.RemotingFormat = set Binary;
by wbbigdave at 2012-10-22 06:23:01
Help?
by wbbigdave at 2012-10-23 07:17:12
Solved with the answer here

http://stackoverflow.com/questions/12857716/extract-millisecond-data-from-sql-stored-procedure