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!

$\endgroup$
  • 1
    $\begingroup$ Just use DatabaseLink` $\endgroup$ – user5601 Mar 3 at 21:26
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$
  • $\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$ – MathematicaUser Mar 3 at 18:59
  • 1
    $\begingroup$ @MathematicaUser I agree. The work is underway regarding that. $\endgroup$ – Leonid Shifrin Mar 3 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.