1

I have a datetime column on SQL Server:

Column('LAST_SEEN_DTS', DATETIME(), table=<mytable>)

I want to increase the date by one day.

This is an excerpt of the column in question:

    last_seen_dts
0   2017-06-12 15:36:05.263
1   2017-06-12 21:43:16.407
2   2017-06-13 09:02:59.457
3   2017-06-13 15:36:44.183
4   2017-06-14 03:04:07.010
5   2017-06-14 11:51:40.603
6   2017-06-15 02:53:12.373
7   2017-06-15 11:46:16.560
8   2017-06-16 02:44:12.110
9   2017-06-16 03:19:30.307

One way I've tried that semi works is this:

q = select([
    table.c.LAST_SEEN_DTS,
    (func.cast(table.c.LAST_SEEN_DTS, DATE) + timedelta(days=1)).label('date1')
]).distinct().limit(10)

One day is added to the date, however, the year is also increased by 70 years:

    date1
0   2087-06-13
1   2087-06-13
2   2087-06-14
3   2087-06-14
4   2087-06-15
5   2087-06-15
6   2087-06-16
7   2087-06-16
8   2087-06-17
9   2087-06-17

I first tried to use the dateadd function as such:

q = select([
    table.c.LAST_SEEN_DTS,
    func.dateadd('day', 1, func.cast(table.c.LAST_SEEN_DTS, DATE)).label('date1')
]).distinct().limit(10)

This doesn't work, because the day option cannot be specified as string. I've tried to find it as a constant, but neither sqlalchemy nor sqlalchemy.dialects.mssql seem to have it.

How do I do this on SQL Server with SQLAlchemy?

EDIT:

I know that I can just use raw SQL like text("cast(dateadd(day, 1, LAST_SEEN_DTS) as DATE)"), but that kind of defeats the purpose of using SQLAlchemy in the first place.

lukas
  • 121
  • 7
  • some help may be here - https://stackoverflow.com/questions/15572292/using-dateadd-in-sqlalchemy – mkRabbani Jun 05 '20 at 11:10
  • I've seen this question, but that Interval syntax is not available on SQL Server. The dateadd function works differently. I've also tried to do some acrobatics with the `bindparam` from SQLAlchemy, unsuccessfully. – lukas Jun 05 '20 at 11:19
  • In sqlserver it should be dateadd(d,1,your_date) – mkRabbani Jun 05 '20 at 11:22
  • Exactly, the question is how to specify the `d` option through sqlalchemy. – lukas Jun 05 '20 at 11:23
  • check the accepted answer here - https://pythonpedia.com/en/knowledge-base/15572292/using-dateadd-in-sqlalchemy this should help. – mkRabbani Jun 05 '20 at 11:34
  • That is the same answer as from the SO question above. As you can see in my question, using `timedelta` has some weird behaviour as it increases the year. The comment on that answer also reads *Only on Postgresql with psycopg2 (and maybe mysql also) does simple date arithmetic as above translate into what the DB expects*. – lukas Jun 05 '20 at 11:39
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/215364/discussion-between-mkrabbani-and-lukas). – mkRabbani Jun 05 '20 at 11:55

1 Answers1

1

You can try with this below script-

q = select([
table.c.LAST_SEEN_DTS,
func.dateadd('D', 1, func.cast(table.c.LAST_SEEN_DTS, DATE)).label('date1')
]).distinct().limit(10)
mkRabbani
  • 16,295
  • 2
  • 15
  • 24