Date format when exported to CSV using Powershell

Question

I Have a powershell script that executes a sql query, then exports the information to a CSV file. However the CSV file reformats all DateTimes entry to mm/d/yyyy hh:mm:ss. I need the entries to be in the format yyyy-mm-dd-hh.mm.ss.00

$SqlCmd.CommandText = $sqlSelectCVS
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($export)

#Close the SQL connection
$SqlConnection.Close()

#Creates the New file with out Column header inforamtion
$export.tables[0] |export-csv -Path ($ExportTo + '\' + $ExportName) -notypeinformation 
(Get-Content ($ExportTo + '\' + $ExportName)| Select-Object -Skip 1) | Set-Content      ($ExportTo + '\' + $ExportName)

The database connections and query work just fine. Also as a side note Ive tried opening it in notepad with no luck and the date is a future date.. I am still very new to powershell so if you could explain any suggestions so i could understand it, i would appreciate it.

Thanks

Austin T French · Accepted Answer · 2013-09-17 18:54:34Z

1

You can use .NET libraries in PowerShell to format the string, like so:

"{0:yyyy-mm-dd-hh.hh.mm.ss.00}" -f [datetime]$stringOrWhateverDateVariable

$stringOrWhateverDateVariable would be the date variable from the SQL table.

I cannot test on an SQL datababe, but it works for strings to your DateTime format.

answered Sep 17, 2013 at 18:54

Austin T French

5,1801 gold badge29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2788724 Over a year ago

Yours works for write-output. but when i transfer it over to a CSV file it changes to "length" "25"

Austin T French Over a year ago

@user2788724 Can you update the question , append an edit at the end with your current export with the method I listed?

Collectives™ on Stack Overflow

Date format when exported to CSV using Powershell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related