Query SQL Date Time With Epoch

PowerShell Help
1

Hello,

I am created a script that queries a SQL database we use for our ticketing system. The script connects to an ODBC and queries the selected groups tickets and is supposed to search from date ranges (I am trying just a few days but it could be from a few weeks to months). I have it all complete except the fact when I query tickets it pulls every ticket instead of the ranges. The database does date/time in epoch format and since I am fairly new to SQL and PS I am not sure how to fit that into a SQL select function.

Can anyone please offer some suggestions?

Here is my code:

#When attempting queries - Most are HPD:Help Desk - That will not work here.  It needs to be
#HPD_Help_Desk

## Login Information - Using CAC Credentials doesn't work yet - have to use Username and Password
$id = Get-Credential "domain\$("username")"
$userId = $id.UserName.split("\")[1].tolower()
$password = $id.GetNetworkCredential().Password 
    write-host "Running Query"

    ##### Start the database connection and set up environment
    $DbString="DSN=ODBC Driver;UID=$userID;PWD=$password"
    add-type -path 'PathtoDll'
    $DBConnection=New-Object System.Data.Odbc.OdbcConnection ($SelectStatement)
    $DBCommand=New-Object System.Data.Odbc.OdbcCommand
    $DBConnection.ConnectionString=$DbString
    $DBConnection.Open()
    $DBCommand.Connection=$DBConnection


##What You Want to Query
    $DBCommand.CommandText="SELECT 
    Incident_Number,
    Assignee, 
    Reported_Date  FROM 
    HPD_Help_Desk WHERE 
    Assigned_Group ='1'
    OR Assigned_Group ='2'
    OR Assigned_Group ='3'
    OR Assigned_Group ='4'
    AND Reported_Date = 'to date'"
    
## Execute Query
    $DBResult=$DBCommand.ExecuteReader()
    $UserTable=New-Object system.data.datatable
    $UserTable.load($DBResult) 
    $UserTable | Format-Table

Any help or suggestions would be appreciated!

Thank you!

2

Which epoch? There are several in computing and depending on which one your database uses it may be easy or hard(ish) to calculate.

If it uses seconds since the UNIX epoch (January 1st 1970) then it’s straightforward as you can use Get-Date and the -UFormat parameter to return the number of seconds elapsed since epoch for any given date:

#Number of seconds since UNIX epoch right now
Get-Date -UFormat %s

#Number of seconds since UNIX epoch on Jan 2nd 1970
Get-Date '02/01/1970' -UFormat %s

You can assign the result to a variable and include it in your query:

$date = Get-Date '23/03/2017' -UFormat %s

##What You Want to Query
    $DBCommand.CommandText="SELECT 
    Incident_Number,
    Assignee, 
    Reported_Date  FROM 
    HPD_Help_Desk WHERE 
    Assigned_Group ='1'
    OR Assigned_Group ='2'
    OR Assigned_Group ='3'
    OR Assigned_Group ='4'
    AND Reported_Date = '$date'"
3

Thanks Matt,

Since I am exporting it to a CSV I changed the way the $UserTable is used on the end to:
$UserTable | Where{$_.Reported_Date -gt ((Get-Date).AddDays(-$DateRange))}| Export-CSV “filepath\test.csv”

Where $DateRange is I have it to where the user is able to enter however many days they want to go back. I am going to create another version of what you provided too.