Change date format in PowerShell Export SQL to CSV script

Question

I have a PowerShell script which exports several tables at once. The script works as a charm, but only i can't change the date formatting of the dates.

A few fields concern dates as defined in SQL, when i try to export these to .CSV a date-time format is used, but i need to keep this as date format.

Export format of date fields are yyyy-MM-dd HH:mm:ss, but the desired export format is yyyy-MM-dd.

Is there a possibility to adjust the script so these dates are shown correctly? The below is the script which is use:

#To configure
$SQLServer = "<Server>"  
$GUID = "<Project>"
$delimiter = ","
$Folder = "<Folder>"

$CY = $Folder + "\CY.csv"
$SQLDBName = "<database>"  

#SQL CY 
"CY downloading, line count below"
$SqlQuery = "SELECT * FROM <database>"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection  
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = SSPI;"  
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
$SqlCmd.CommandText = $SqlQuery  
$SqlCmd.Connection = $SqlConnection  
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
$SqlAdapter.SelectCommand = $SqlCmd   
#Creating Dataset  
$DataSet = New-Object System.Data.DataSet  
$SqlAdapter.Fill($DataSet)  
$DataSet.Tables[0] | export-csv -Delimiter $delimiter -Path $CY -NoTypeInformation 

I already have tried changing the Select * From statement to include CAST() or Convert() for the dates, but this didn't gave any different results.

I'm quite stuck on this last item and would be great if someone could help me out. Thanks in advance.

Answer 1

I resolved the issue by adjusting my SQL query. I don't know why this initially didn't work, but after changing the date fields in the query to the following it worked.

Convert(varchar,[DATEFIELD],23) as 'DATEFIELD'

This topic can be closed.