0

When I execute a function that returns a datetime (like GETDATE()) in SQL Server Management Studio the output is formatted in the user's "culture" (region/language settings). However when that same query is executed in Powershell through the Invoke-Sqlcmd cmdlet the output is formatted in US date format (which I assume is the Invariant culture). Other Cmdlets like Get-Date return the datetime in the CurrentCulture.

How can I ensure that the output of Invoke-Sqlcmd respects the CurrentCulture settings?

Minimal code sample to demonstrate

$result = Invoke-Sqlcmd -query "select GETDATE() as datetime" -ServerInstance ...
$now = Get-date -format "yyyy-MM-dd HH:mm:ss"
if ( $result.datetime -eq $now) {
  Write-output "ok"
} else {
  Write-warning "dates mismatch '$($result.datetime)' should be '$now'"
}

The warning shows the first datetime displayed in US format and the second datetime in the format specified

Bram
  • 819
  • 1
  • 9
  • 24
  • You should ifx the script so it *doesn't* use locale-specific literals when dates or numbers should be used. `GetDate()` has **no** format - it's just a strongly typed, binary, `datetime` value. Are you converting it to a string perhaps? Post your script – Panagiotis Kanavos Jan 31 '17 at 15:52
  • If you use `INSERT INTO MyTable (MyCol) VALUES(GetDate())` the locale doesn't matter. If you write instead `EXEC 'INSERT ...' + GETDATE() + '..'` you have a bug. Your script will fail if the locale changes, *and* it will be vulnerable to a lot of other problems, including SQL injection – Panagiotis Kanavos Jan 31 '17 at 15:54
  • @PanagiotisKanavos: I'm not using this to insert. The purpose of the script is to verify that a custom function returns the current datetime correctly. You're absolutely right that the function (like `GETDATE()`) returns a System.DateTime object. The formatting happens in the Write-Warning call that displays the result and the expected value (which is passed to the function as a string). If you post your first comment as an answer I'll accept it. – Bram Jan 31 '17 at 16:14

1 Answers1

0

$result.datetime will be of type datetime, while $now will be of type string. That's why your comparison and output do not work as intended. You could just go without converting the current date to a string:

...
$now = Get-Date
if ($result.datetime -eq $now) {
...

But keep in mind that you would then compare both values using a precision of milliseconds. According to your format string, I assume you want only a precision of seconds. Then you can do the following:

...
$now = Get-Date
$format = 'yyyy-MM-dd HH:mm:ss'
if ($result.datetime.toString($format) -eq $now.toString($format)) {
...
stackprotector
  • 10,498
  • 4
  • 35
  • 64
  • Thanks @Thomas. I'll test as soon as possible (which may be a while, working on some other priorities now) and will accept your answer or provide feedback! – Bram Jun 29 '20 at 09:17