Covert datetime as date in a sqlite pythonsql query

[Nicolás López Solano]

Hi,

I need to writte a pythonsql query with a date column computed converting a datetime column. I'm using Cast function to do that but only works for postgresql and i need it works for sqlite too. I tried to use anothers pythonsql functions like ToDate or DatePart but only work for postgresql too.

Any sugest?

Thanks

[Sergi Almacellas Abellana]

El 26/06/17 a les 16:07, Nicolás López Solano ha escrit:

Can you show which current code do you have?

Have you tried:

Cast(column, 'DATE')

Which errors do you have?


--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

[Nicolás López Solano]

2017-06-26 16:10 GMT+02:00 Sergi Almacellas Abellana <se...@koolpi.com>:

El 26/06/17 a les 16:07, Nicolás López Solano ha escrit:

Hi,

I need to writte a pythonsql query with a date column computed converting a datetime column. I'm using Cast function to do that but only works for postgresql and i need it works for sqlite too. I tried to use anothers pythonsql functions like ToDate or DatePart but only work for postgresql too.

Can you show which current code do you have?

Have you tried:

Cast(column, 'DATE')

 

Yes i tried. For postgresql it return a correct date but for sqlite it return only the year of the datetime base column.

If i work directly executing sql (select cast(column as date) from mytable) over a sqlite file i get a similar "not good" result. Cast sqlite native functión can't covert to date.

Which errors do you have?

No errors ... only not ok result

--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

--
You received this message because you are subscribed to the Google Groups "tryton-dev" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/tryton-dev/5f0a2c6d-e249-5d57-6019-cc246090498b%40koolpi.com.

[Sergi Almacellas Abellana]

El 26/06/17 a les 16:31, Nicolás López Solano ha escrit:

> Yes i tried. For postgresql it return a correct date but for sqlite it
> return only the year of the datetime base column.
>
> If i work directly executing sql (select cast(column as date) from
> mytable) over a sqlite file i get a similar "not good" result. Cast
> sqlite native functión can't covert to date.

Right, I can reproduce it on my local environemnt. In order to get a
proper date on sqlite you can use:

SELECT Date(column)

instead of

SELECT Cast(column as date)

So I ended up with the following code:

from sql.functions import Function

class SqlLiteDate(Function):
__slots__ = ()

_function = 'DATE'

if backend.name() == 'sqlite':
date = SqliteDate(column)
else:
date = Cast(column, 'DATE')

Hope it helps!

[Nicolás López Solano]

This is just that i need
Thanks a lot

--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

--
You received this message because you are subscribed to the Google Groups "tryton-dev" group.

To view this discussion on the web visit https://groups.google.com/d/msgid/tryton-dev/906ca9f7-71dc-deef-46ee-0210e427d137%40koolpi.com.