RelationalDatabases and dates (Mathematica 12)

Question

As related to the new database functionality in version 12, I am trying to retrieve a specific entity (row) or group of entities (multiple rows) between a date range in an MS SQL database. The database I am looking at has 4 columns, "SKUNumber", "Quantity", "Price", "SoldDate". How can I construct a query (EntityFuncton?) to do that?

The equivalent SQL query would have the WHERE clause be:

WHERE SKUNumber = '1111' 
   AND SoldDate > '2019-05-31 00:00:00.000' 
   AND SoldDate < '2019-09-01 00:00:00.000'

The quick guide can be found:

Thanks!

Answer 1

Admittedly the date and time support for the new relational databases is not yet exactly where we would like it to be.

Under the following assumptions

• You have already registered the database ( as described e.g. in the tutorial you linked)
• Your "SoldDate" column is of the type DATE, not DATETIME
• You actually wanted the less, not greater, inequality sign in the last clause

The query you need will be as follows:

EntityValue[
  FilteredEntityClass[
    "your-table",
    EntityFunction[o, 
      And[
        o["SKUNumber"] == "1111", 
        o["SoldDate"] > DateObject[
           Take[DateList["2019-05-31 00:00:00.000"], 3]
        ] ,
        o["SoldDate"] < DateObject[
           Take[DateList["2019-09-01 00:00:00.000"], 3]
        ]
      ]
    ]
  ],
  {"SKUNumber", "Quantity", "Price", "SoldDate"}
]

where it is important to note that the Take[DateList[...]] part is evaluated on the Wolfram Language side, before the query gets compiled to SQL and executed.