Date Format Issue when exporting to CSV

I am exporting data from SQL to a CSV file, when exporting the date format is changing in the CSV. In SQL the format is YYYY-MM-DD HH:MI, when i try to export through powershell the format is converting into MM/DD/YYYY HH:MI. Can some one help me on this. Below is my powershell script

$OutputPath = "xxx"
$SQLScriptsPath ='xxx'
$ServerList = "xxx"

Get-ChildItem $SqlScriptsPath -Filter *.sql | Sort-Object Name | ForEach-Object {
$File = $_
Get-Content $ServerList | ForEach-Object {
$Server = $_

New-Item "$OutputPath\$Server" -ItemType Directory -Force | Out-Null
$OutputFile = "$OutputPath\$Server\$($File.BaseName)$(Get-Date -uformat %d%m%Y).csv"

try {
Invoke-SqlCmd -QueryTimeout 1000 –ServerInstance $Server -InputFile $File.FullName |
Export-Csv $OutputFile -NoClobber -NoTypeInformation
} catch {
Write-Host $_.Exception.Message -ForegroundColor Red
}
}
}

Parents
No Data
Reply
  • I don't know that much about Excel, but I do know that it stores datetimes as floating point numbers (the same as SQL). The problem is one of display format, and as I understand it, Excel has some limitations. If you highlight the column of dates, right-click and choose "Format Cells", there may be an option for formatting the way you want.

    Alternatively, you can format the datetime as a varchar in your SQL query:

    select convert(varchar(24), datetime_col, 21) 'formatted_date', col1, col2, etc

    I think that's about as good as you're going to get, but I'll be interested to see what others have.

Children
No Data