英文:
Powershell function to check input and return datatype is not returning correct values
问题
I'm using a powershell script to assist with database updates/parsing/etc. Because our database servers do not allow us to do queries and updates within the same script, I need to actually query data, parse it on my computer with powershell, and then build a new update statement from the parsed query. In order to assist with this, I need to correctly cast datatypes between powershell and SQL. I wrote the following function to assist with this, but it is not correctly parsing timestamps, and any timestamp passed to it is causing the function to return 'string' and leave the $setDate variable as $null
$myDate = '5/10/2023'
function getDataType {
param($inputData)
#check if input data is null
if (!$inputData) {
return 'null'
}
#check if variable can be cast as a timestamp
try {
#build an array of possible timestamp formats
$dateFormats = @('M/d/yyyy','M/dd/yyyy','MM/d/yyyy','MM/dd/yyyy','MM-dd-yyyy HH:mm:ss','MM-dd-yyyy HH:mm','yyyy-MM-dd HH.mm.ss')
foreach ($format in $dateFormats) {
if ([DateTime]::TryParseExact($inputData, $format, $null)) {
$global:setDate = [datetime]::ParseExact($inputData, $format, $null).ToString('yyyy-MM-dd HH:mm:ss')
return 'timestamp'
break
}
}
}
catch {
write-verbose 'not a timestamp'
}
#check if data is a number
try {
if ([int]$inputData) {
return 'number'
}
}
catch {
write-verbose 'not a number'
}
#assumes input is a string if it is not null, not a date, and not a number
return 'string'
}
getDataType($myDate) #should return 'timestamp' but returns 'string'
write-host "the date is $setDate" #should return "the date is 2023-05-10 00:00:00" but returns nothing
<details>
<summary>英文:</summary>
I'm using a powershell script to assist with database updates/parsing/etc. Because our database servers do not allow us to do queries and updates within the same script, I need to actually query data, parse it on my computer with powershell, and then build a new update statement from the parsed query. In order to assist with this, I need to correctly cast datatypes between powershell and SQL. I wrote the following function to assist with this, but it is not correctly parsing timestamps, and any timestamp passed to it is causing the function to return 'string' and leave the $setDate variable as $null
```$setDate = $null
$myDate = '5/10/2023'
function getDataType {
param($inputData)
#check if input data is null
if (!$inputData) {
return 'null'
}
#check if variable can be cast as a timestamp
try {
#build an array of possible timestamp formats
$dateFormats = @('M/d/yyyy','M/dd/yyyy','MM/d/yyyy','MM/dd/yyyy','MM-dd-yyyy HH:mm:ss','MM-dd-yyyy HH:mm','yyyy-MM-dd HH.mm.ss')
foreach ($format in $dateFormats) {
if ([DateTime]::TryParseExact($inputData, $format, $null)) {
$global:setDate = [datetime]::ParseExact($inputData, $format, $null).ToString('yyyy-MM-dd HH:mm:ss')
return 'timestamp'
break
}
}
}
catch {
write-verbose 'not a timestamp'
}
#check if data is a number
try {
if ([int]$inputData) {
return 'number'
}
}
catch {
write-verbose 'not a number'
}
#assumes input is a string if it is not null, not a date, and not a number
return 'string'
}
getDataType($myDate) #should return 'timestamp' but returns 'string'
write-host "the date is $setDate" #should return "the date is 2023-05-10 00:00:00" but returns nothing
</details>
# 答案1
**得分**: 2
你的代码主要问题在于你的使用 [`[DateTime]::TryParseExact`](https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tryparse?view=net-7.0) 是错误的，它正在抛出错误，只是因为 `Write-Verbose` 没有 `-Verbose` 参数来产生详细输出，这是一个简化的示例：
```sh
try {
[DateTime]::TryParseExact('5/10/2023', 'M/dd/yyyy', $null)
}
catch {
Write-Verbose 'not a timestamp' -Verbose
}
# 输出: VERBOSE: not a timestamp
看起来你似乎想要多次调用这个函数，你应该注意到重复调用函数是昂贵的，为此我建议你将你的函数改为静态方法 ，它们不受此影响。这里是你想要实现的一个可工作版本：
class MyType {
static [string[]] $Formats = @(
'M/d/yyyy'
'M/dd/yyyy'
'MM/d/yyyy'
'MM/dd/yyyy'
'MM-dd-yyyy HH:mm:ss'
'MM-dd-yyyy HH:mm'
'yyyy-MM-dd HH.mm.ss'
)
static [string] GetDataType([object] $inputData, [ref] $out) {
if([string]::IsNullOrWhiteSpace($inputData)) {
return 'null or empty string'
}
if([int]::TryParse($inputData, [ref] $null)) {
return 'its an integer'
}
# 这里不需要循环，`TryParseExact` 有一个 `string?[]? formats` 重载方法来处理这个 ;)
$isDate = [datetime]::TryParseExact(
$inputData,
[MyType]::Formats,
[cultureinfo]::InvariantCulture,
[System.Globalization.DateTimeStyles]::None, # 你应该定义这个
$out)
if($isDate) {
return 'its a datetime'
}
return 'undetermined string'
}
}
$myDate = '5/10/2023'
$myOutVar = [datetime]::new(0)
$result = [MyType]::GetDataType($myDate, [ref] $myOutVar)
if($result -eq 'its a datetime') {
return $myOutVar
}
$result
英文:
The main issue with your code is that your use [DateTime]::TryParseExact is incorrect and its throwing an error, you're just not seeing it because Write-Verbose doesn't have a -Verbose argument to produce verbose output, a minimal example:
try {
[DateTime]::TryParseExact('5/10/2023', 'M/dd/yyyy', $null)
}
catch {
Write-Verbose 'not a timestamp' -Verbose
}
# Outputs: VERBOSE: not a timestamp
It also seems you're looking to make repeated calls to this function, you should note that repeated function calls is expensive, for that I would recommend you to change your function for a static method which don't suffer from this. Here is a working version of what you were looking to accomplish:
class MyType {
static [string[]] $Formats = @(
'M/d/yyyy'
'M/dd/yyyy'
'MM/d/yyyy'
'MM/dd/yyyy'
'MM-dd-yyyy HH:mm:ss'
'MM-dd-yyyy HH:mm'
'yyyy-MM-dd HH.mm.ss'
)
static [string] GetDataType([object] $inputData, [ref] $out) {
if([string]::IsNullOrWhiteSpace($inputData)) {
return 'null or empty string'
}
if([int]::TryParse($inputData, [ref] $null)) {
return 'its an integer'
}
# no need for a loop here, `TryParseExact` has an `string?[]? formats` overload for this ;)
$isDate = [datetime]::TryParseExact(
$inputData,
[MyType]::Formats,
[cultureinfo]::InvariantCulture,
[System.Globalization.DateTimeStyles]::None, # You should define this one
$out)
if($isDate) {
return 'its a datetime'
}
return 'undetermined string'
}
}
$myDate = '5/10/2023'
$myOutVar = [datetime]::new(0)
$result = [MyType]::GetDataType($myDate, [ref] $myOutVar)
if($result -eq 'its a datetime') {
return $myOutVar
}
$result
答案2
得分: 0
如果您不需要指定自定义日期时间格式，类似Get-Date命令应该可以捕获有效的日期和时间戳。如果您想考虑带有句点的格式，Get-Date无法处理，可以使用以下替换...
# 以下示例捕获所需字符串格式中的所有日期
$SetDates = @(
'5/9/2023',
'5/10/2023',
'05/9/2023',
'05/10/2023',
'05-10-2023 12:05:02',
'05-10-2023 12:05',
'2023-05-10 12.05.02'
) | ForEach-Object {
Get-Date -Date $($_ -replace '\.','/') -Format 'yyyy-MM-dd HH:mm:ss'
}
$SetDates
2023-05-09 00:00:00
2023-05-10 00:00:00
2023-05-09 00:00:00
2023-05-10 00:00:00
2023-05-10 12:05:02
2023-05-10 12:05:00
2023-05-10 12:05:02
(Note: The provided code is PowerShell for manipulating date formats.)
英文:
If you don't need to specify custom datetime formats, something like the Get-Date cmdlet should catch valid dates & timestamps. If you want to consider formats with periods that would fail with Get-Date, use a replace like so...
# Below example captured all dates in desired string format
$SetDates = @(
'5/9/2023',
'5/10/2023',
'05/9/2023',
'05/10/2023',
'05-10-2023 12:05:02',
'05-10-2023 12:05',
'2023-05-10 12.05.02'
) | ForEach-Object {
Get-Date -Date $($_ -replace '\.',':') -Format 'yyyy-MM-dd HH:mm:ss'
}
$SetDates
2023-05-09 00:00:00
2023-05-10 00:00:00
2023-05-09 00:00:00
2023-05-10 00:00:00
2023-05-10 12:05:02
2023-05-10 12:05:00
2023-05-10 12:05:02
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论