SQL query with the BETWEEN-command, in CRAN R to select dates

Question

The ff. example is taken from: https://www.datenbanken-verstehen.de/sql-tutorial/sql-between-befehl/

Just started to learn SQL in R. I want to get a list of dates in between those two dates as set out in 'newdf.Mitarbeiter4'. Can anyone please help me pointing out the mistake in my query? There are similar questions about this query, but somehow I still could not use the answers in identifying my mistake.

Mitarbeiter4 <- data.frame( Nachname = c( "Wegner", "Müller", "Schulz", "Richter", "Schröder" ),
                            Vorname = c( "Lutz", "Melanie", "Dorothea", "Heiko", "Lukas" ),
                            Geburtstag = c( '12.12.1983', '01.04.1978', '13.02.1990', '15.08.1995', '24.07.1980' )
                          )

Mitarbeiter4

newdf.Mitarbeiter4 <- sqldf( "SELECT Nachname, Vorname, Geburtstag from Mitarbeiter4 WHERE Geburtstag BETWEEN '1980.01.01' AND '1993.01.01' " )

newdf.Mitarbeiter4

which gives me the ff. result:

[1] Nachname Vorname Geburtstag

<0 Zeilen> (oder row.names mit Länge 0)

I tried for the dates both "." and "/" as in the other questions asked, but still no success on my side.

As I have problems with the correct formatting could I please refer the kind person who replies to my question to the result as in the tutorial quoted above under:

"Das Ergebnis würde wie folgt aussehen:"

akrun · Accepted Answer · 2019-12-25 17:33:21Z

2

It may be better to convert to Date class and then do

library(sqldf)
Mitarbeiter4$Geburtstag <- as.Date(Mitarbeiter4$Geburtstag, "%d.%m.%Y")
start <- as.Date("1980-01-01")
end <- as.Date("1993-01-01")
fn$sqldf("SELECT Nachname, Vorname, Geburtstag from Mitarbeiter4 
           WHERE Geburtstag between $start and $end")
# Nachname  Vorname Geburtstag
#1   Wegner     Lutz 1983-12-12
#2   Schulz Dorothea 1990-02-13
#3 Schröder    Lukas 1980-07-24

Or using tidyverse

library(dplyr)
library(lubridate)
Mitarbeiter4 %>% 
    filter(between(dmy(Geburtstag), as.Date("1980-01-01"), as.Date("1993-01-01")))
#   Nachname  Vorname Geburtstag
#1   Wegner     Lutz 12.12.1983
#2   Schulz Dorothea 13.02.1990
#3 Schröder    Lukas 24.07.1980

Or in base R

subset(Mitarbeiter4, as.Date(Geburtstag, "%d.%m.%Y") > as.Date("1980-01-01") & 
     as.Date(Geburtstag, "%d.%m.%Y") < as.Date("1993-01-01"))
#   Nachname  Vorname Geburtstag
#1   Wegner     Lutz 12.12.1983
#3   Schulz Dorothea 13.02.1990
#5 Schröder    Lukas 24.07.1980

NOTE: the as.Date conversion on the 'Geburtstag' column can be done before the filtering step in subset to avoid multiple calls to as.Date

edited Dec 25, 2019 at 17:33

answered Dec 25, 2019 at 17:22

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andreas Over a year ago

continued: sorry I pressed RETURN, well, yr first answer is great, it now works fine, in another Tutorial I came across the "fn-sql" command, which motivates me now to work on it, because this BETWEEN command is highly necessary on a work which awaits me.

Collectives™ on Stack Overflow

SQL query with the BETWEEN-command, in CRAN R to select dates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related