date format getting changed from yyyy-mm-dd hh:mm:ss:fff to dd-mm-yyyy hh:mm

[kamleshyadav95]

Hi All,

I am trying to export data from sql server database to csv. data is getting export perfectly in csv file but date format is getting from "yyyy-mm-dd hh:mm:ss:fff" to "dd-mm-yyyy hh:mm".

please help to correct it

Appreciate quick response.

# Define database connection details
$server = "YourServerName"
$database = "YourDatabaseName"
$username = "YourUsername"

# Retrieve and decrypt the password
$encryptedPassword = Get-Content "C:\path\to\encryptedPassword.txt" | ConvertTo-SecureString
$credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $encryptedPassword

# SQL query
$query = "
SELECT *
FROM YourTable
"

$data = Invoke-Sqlcmd -ServerInstance $server -Database $database -Credential $credentials -Query $query
$data | Export-Csv -Path "C:\path\to\your\output.csv" -NoTypeInformation -Encoding UTF8

Write-Host "Data exported successfully to C:\path\to\your\output.csv"

[#2]

@kamleshyadav95 

Hi, Kamlesh.

You can change the format used via the built-in $host object.

Here's a short, manual example showing the bare minimum. First, we back up the original culture settings before changing the two relevant properties. Finally, we restore the original values.

Applying this to a script, the following skeleton code takes the same approach, where we'll use a try .. catch .. finally block to ensure that the original settings are always restored, even in the event of a fatal exception.

Skeleton example

try
{
    #region Back up the current culture values and change those used in DateTime.ToString() to influence this session's formatting.
    $OriginalCulture = $host.CurrentCulture.DateTimeFormat.Clone();
    $host.CurrentCulture.DateTimeFormat.LongDatePattern = "yyyy-MM-dd";
    $host.CurrentCulture.DateTimeFormat.LongTimePattern = "HH:mm:ss.fff";
    #endregion

    <#
        Insert the existing script in here...
        ...
        ...
    #>
}
catch
{
    # Not doing anything special here. Just re-throwing the existing exception.
    throw;
}
finally
{
    #region Unconditionally restore the original DateTime patterns.
    $host.CurrentCulture.DateTimeFormat = $OriginalCulture;
    #endregion
}

Finally, here's a full example using your existing script (which I haven't check in any way, shape or form).

Full example

try
{
    #region Back up the current culture values and change those used in DateTime.ToString() to influence this session's formatting.
    $OriginalCulture = $host.CurrentCulture.DateTimeFormat.Clone();
    $host.CurrentCulture.DateTimeFormat.LongDatePattern = "yyyy-MM-dd";
    $host.CurrentCulture.DateTimeFormat.LongTimePattern = "HH:mm:ss.fff";
    #endregion

    #region Your existing script has been copied-and-pasted verbatim into this region.
    # Define database connection details
    $server = "YourServerName"
    $database = "YourDatabaseName"
    $username = "YourUsername"

    # Retrieve and decrypt the password
    $encryptedPassword = Get-Content "C:\path\to\encryptedPassword.txt" | ConvertTo-SecureString
    $credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $encryptedPassword

    # SQL query
    $query = "
    SELECT *
    FROM YourTable
    "

    $data = Invoke-Sqlcmd -ServerInstance $server -Database $database -Credential $credentials -Query $query
    $data | Export-Csv -Path "C:\path\to\your\output.csv" -NoTypeInformation -Encoding UTF8

    Write-Host "Data exported successfully to C:\path\to\your\output.csv"
    #endregion
}
catch
{
    # Not doing anything special here. Just re-throwing the existing exception.
    throw;
}
finally
{
    #region Unconditionally restore the original DateTime patterns.
    $host.CurrentCulture.DateTimeFormat = $OriginalCulture;
    #endregion
}

Cheers,

Lain

[#3]

Thanks a lot Lain !!

I am getting below output.
2001-07-09 22 22:00:15.000 but I need like 2001-07-09 22:00:15.000

[#4]

@kamleshyadav95 

Check your value for LongTimePattern.

It looks like you've specified the "HH" component twice.

Cheers,

Lain

[#5]

Hi Lain,

Thanks for response i am using same format as needed but "HH" component extra getting added.

$OriginalCulture = $host.CurrentCulture.DateTimeFormat.Clone();
$host.CurrentCulture.DateTimeFormat.LongDatePattern = "yyyy-MM-dd";
$host.CurrentCulture.DateTimeFormat.LongTimePattern = "HH:mm:ss.fff";

[#6]

@kamleshyadav95 

That's unexpected and not in line with my screenshot from above, which does not feature the extra "HH".

What happens if you run the following line after the original three you've repeated above?

$host.CurrentCulture.DateTimeFormat | Format-List -Property *pattern;

You should see an enumeration of the various formats as shown below, with the change in format reflected in the FullDateTimePattern property:

Cheers,

Lain

[#7]

Hi Lain,
Yes, Agree I can see that but not sure why this is not coming correctly.

Kamlesh