CODEWITHSUNDEEP
sqlr
hahakomisch
hahakomisch

Reputation: 49

sql query in R, comparing two dates

I have a DB with a column "date_time" which looks like 2020-08-11 18:06:37. What I need now is to compare only the date with today's date.

e.g. 20200811 = 20200911

And I need it to compare it with the date 7 days before.

So I finally need this 20200811 = 20200911 - 7

I tried this, but it didn't work out.

abc <- paste("
              SELECT *
              FROM abcdef
              WHERE convert(dateime, abcdef.date_time, 114) = convert(datetime, ",Sys.Date(),",114)-7
             ")

Upvotes: 0

Views: 246

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Your code looks like SQL Server. You should do this in the database.

Assuming your date is really stored as a date/time and not as a string, I would recommend:

SELECT *
FROM abcdef
WHERE convert(date, abcdef.date_time) = dateadd(day, -7, convert(date, getdate()))

Upvotes: 1

Related Questions