CODEWITHSUNDEEP
sqlitepowershell
Melon Man
Melon Man

Reputation: 185

Pulling Data With Date Between Two Days using SQLite in Powershell

currently i'm trying to pull data from dates 9 days ago. The problem i'm currently having is that even tho the query isn't running into errors, i'm still not pulling the data I'm looking for. I'm starting to wonder if it has something to do with the formatting of the date field in the data based.

Here's what the dates look like in the database.

2019-07-29 21:17:28.4516886+00:00
2019-07-29 21:25:16.8570866+00:00
2019-07-29 21:25:16.908947+00:00
2019-07-29 21:26:04.9377803+00:00
2019-07-29 21:26:05.2968044+00:00

And here is the current code I have for the query, and calling the query to pull it from the database.

$Database = "C:\main\main.db"

$query = "SELECT [email], [campaign_id], [status], [modified_date],* FROM [main].[results] WHERE [modified_date] BETWEEN (SELECT DATETIME('now','-8 day')) and (SELECT DATETIME('now','-10 day'));"

$emaildata = Invoke-SqliteQuery -DataSource $Database -Query $query  | Select @{Name="EmailAddress";Expression={$_.Email}}, @{Name="Campaign_ID";Expression={$_.campaign_id}} , @{Name="Status";Expression={$_.status}}, @{Name="Date_Modified";Expression={$_.modified_date}}
write-output $emaildata

If I do NOT BETWEEN, then it pulls everything, while just BETWEEN pulls nothing.

So i'm hoping to figure out what I need to change to have the dates pulled correctly. Thanks!

Upvotes: 0

Views: 97

Answers (1)

forpas
forpas

Reputation: 164089

Change your statement to this:

BETWEEN DATETIME('now','-10 day') and DATETIME('now','-8 day')

DATETIME('now','-10 day') is before DATETIME('now','-8 day').
Also you don't need SELECT to get DATETIME('now','-10 day')

Upvotes: 2

Related Questions