Sir Rawlins wrote: > Hello Guys, > > I've got a query which I'm currently running as literal SQL against a > MySQL database. I'm looking to create a ported version of the query > for SQLite but am totally new to that platform so am looking for a > little help with my date/time functions, I'm hoping someone here will > have a little more experience with SQLite and be able to help out. > > In addition to making these changes to the literal SQL I'm wondering > if the query can be rewritten in a more SQLAlchemy style using > functions rather than literal SQL so that it is more portable in > future? Or is that going to be tricky? > > The query, or at least the WHERE clause can be found in this pastebin > snippet. > > http://pastebin.com/m24c39a4f > > I appreciate any help you guys can offer to get me started. I > understand I can get the current date within SQLite by running date > ('now') however some of the more tricky modifiers for getting > DayOfWeek and DayOfMonth are beyond my understanding at the moment. > > Thanks, > > Rob > SQLite's strftime() function will give you equivalent results: TO_DAYS(x) -> CAST(strftime('%J', x) AS INTEGER) MOD(x, y) -> x % y DAYOFMONTH(x) -> CAST(strftime('%d', x) AS INTEGER) DAYOFYEAR(x) -> CAST(strftime('%j', x) AS INTEGER) DAYOFWEEK(x) -> CAST(strftime('%w', x) AS INTEGER) + 1
AFAIK SQLAlchemy does not provide database-neutral functions for dates, so you are stuck with database-specific queries. -Conor --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---