6
$\begingroup$

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!

Improve this question
$\endgroup$
1
  • 1
    $\begingroup$ Just use DatabaseLink` $\endgroup$ Commented Mar 3, 2020 at 21:26

1 Answer 1

Reset to default
4
$\begingroup$

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.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Leonid, thank you for the detailed answer. I think adding Date & Time support is quite vital with respect to most SQL work. Thank you! $\endgroup$ Commented Mar 3, 2020 at 18:59
  • 1
    $\begingroup$ @MathematicaUser I agree. The work is underway regarding that. $\endgroup$ Commented Mar 3, 2020 at 19:08

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.