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

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