Timestamp issues with Powershell

Question

I have a small powershell script that pulls the last hour of punch data from a sql db, it then outputs that data to a .csv file. The script is working, but the timestamp is like this: hh:mm:ss.xxx, i need it to be only hh:mm, Any help would be greatly appreciated!

Below is the script and a snippet of the output:

sqlcmd -h-1 -S ZARIRIS\IRIS -d IA3000SDB -Q "SET NOCOUNT ON; Select Distinct TTransactionLog_1.DecisionTimeInterval,
TTransactionLog_1.UserID, TTransactionLog_1.OccurDateTime, TTransactionLog_1.StableTimeInterval

From TTransactionLog_1
Inner join TSystemLog1 On TTransactionLog_1.NodeID=TSystemLog1.NodeID
Inner join TUser On TTransactionLog_1.UserID=Tuser.UserID
where TSystemLog1.NodeID = 3 and TTransactionLog_1.OccurDateTime >= dateadd(HOUR, -1, getdate())" -s  "," -W -o  "C:\atr\karen\adminreport3.csv"

Get-Content "C:\ATR\Karen\adminreport3.csv" | ForEach-Object {$_ -replace "44444444","IN PUNCH"} | ForEach-Object {$_ -replace "11111111","OUT PUNCH"} | Set-Content "C:\ATR\Karen\punchreport1.csv" -Force

Output: (where i need the hh:mm format, it needs to read 12:08, not 12:08:19.000)

112213,2022-10-31 12:08:19.000,OUT PUNCH

Does this csv have headers? Why read a csv file as an array of strings instead of having it parsed out using `Import-Csv` ? — Theo, Oct 31 '22 at 20:13
This is the format required by ADP to upload our timeclock punches to them. — user2414682, Oct 31 '22 at 20:30

score 0 · Answer 1 · answered Oct 31 '22 at 20:41

It would probably be best if your script were to write out a date formatted the way you want in the first place, but if that's not an option, you really should consider using Import-Csv and Export-Csv to manipulate the data inside. If the standard quoted csv output is something you don't want, please see this code to safely remove the quotes where possible.

Having said that, here's one way of doing it in a line-by-line fashion:

Get-Content "C:\ATR\Karen\adminreport3.csv" | ForEach-Object {
    $line = $_ -replace "44444444","IN PUNCH" -replace "11111111","OUT PUNCH"
    $fields = $line -split ','
    # reformat the date by first parsing it out as DateTime object
    $fields[1] = '{0:yyyy-MM-dd HH:mm}' -f [datetime]::ParseExact($fields[1], 'yyyy-MM-dd HH:mm:ss.fff',$null)
    # or use regex on the date and time string as alternative
    # $fields[1] = $fields[1] -replace '^(\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}).*', '$1'

    # rejoin the fields with a comma
    $fields -join ','
} | Set-Content "C:\ATR\Karen\punchreport1.csv" -Force

Timestamp issues with Powershell

1 Answers1