MONTH function

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

MONTH function

Lucas
Hello,

I am testing SQLIte as we are considering to change our DB Server but I
found that a feature is missing, the function Month:

SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1

This works perfect under MySQL or MSSQL.

Do you plan to introduce MONTH and YEAR functions?.


Also, the function strftime('%m', fecha) does not solve the issue:

SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1


Please, any idea of how to solve it.


Thank you. Best regards,

Lucas de Beltran
Caritas España


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MONTH function

Simon Slavin-3

On 23 Jun 2013, at 8:45pm, Lucas <[hidden email]> wrote:

> Also, the function strftime('%m', fecha) does not solve the issue:
>
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1

We want to know what format you are storing your dates in.  Can you do this:

SELECT fecha FROM apuntes LIMIT 3

and tell us the results ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MONTH function

Patrik Nilsson-2
In reply to this post by Lucas
"select strftime('%m',datetime('now'))='06'"
1

"select strftime('%m',datetime('now'))=6"
0

It is considered as a string, not a number.


On 06/23/2013 09:45 PM, Lucas wrote:

> Hello,
>
> I am testing SQLIte as we are considering to change our DB Server but I
> found that a feature is missing, the function Month:
>
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
>
> This works perfect under MySQL or MSSQL.
>
> Do you plan to introduce MONTH and YEAR functions?.
>
>
> Also, the function strftime('%m', fecha) does not solve the issue:
>
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
>
>
> Please, any idea of how to solve it.
>
>
> Thank you. Best regards,
>
> Lucas de Beltran
> Caritas España
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

--
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MONTH function

Patrik Nilsson-2
In reply to this post by Lucas
You can write:

"select cast(strftime('%m',datetime('now')) as integer)=6"
1


On 06/23/2013 09:45 PM, Lucas wrote:

> Hello,
>
> I am testing SQLIte as we are considering to change our DB Server but I
> found that a feature is missing, the function Month:
>
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
>
> This works perfect under MySQL or MSSQL.
>
> Do you plan to introduce MONTH and YEAR functions?.
>
>
> Also, the function strftime('%m', fecha) does not solve the issue:
>
> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
>
>
> Please, any idea of how to solve it.
>
>
> Thank you. Best regards,
>
> Lucas de Beltran
> Caritas España
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

--
ASCII ribbon campaign ( )
 against HTML e-mail   X
 www.asciiribbon.org  / \
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MONTH function

Gabor Grothendieck
Which can also be written as:

"select cast(strftime('%m','now') as integer)=6"

On Sun, Jun 23, 2013 at 4:11 PM, Patrik Nilsson
<[hidden email]> wrote:

> You can write:
>
> "select cast(strftime('%m',datetime('now')) as integer)=6"
> 1
>
>
> On 06/23/2013 09:45 PM, Lucas wrote:
>> Hello,
>>
>> I am testing SQLIte as we are considering to change our DB Server but I
>> found that a feature is missing, the function Month:
>>
>> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
>>
>> This works perfect under MySQL or MSSQL.
>>
>> Do you plan to introduce MONTH and YEAR functions?.
>>
>>
>> Also, the function strftime('%m', fecha) does not solve the issue:
>>
>> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
>>
>>
>> Please, any idea of how to solve it.
>>
>>
>> Thank you. Best regards,
>>
>> Lucas de Beltran
>> Caritas España
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> ASCII ribbon campaign ( )
>  against HTML e-mail   X
>  www.asciiribbon.org  / \
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MONTH function

Gabor Grothendieck
Or even:

"select cast(strftime('%m') as integer)=6"

On Sun, Jun 23, 2013 at 4:16 PM, Gabor Grothendieck
<[hidden email]> wrote:

> Which can also be written as:
>
> "select cast(strftime('%m','now') as integer)=6"
>
> On Sun, Jun 23, 2013 at 4:11 PM, Patrik Nilsson
> <[hidden email]> wrote:
>> You can write:
>>
>> "select cast(strftime('%m',datetime('now')) as integer)=6"
>> 1
>>
>>
>> On 06/23/2013 09:45 PM, Lucas wrote:
>>> Hello,
>>>
>>> I am testing SQLIte as we are considering to change our DB Server but I
>>> found that a feature is missing, the function Month:
>>>
>>> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE MONTH(FECHA) = 1
>>>
>>> This works perfect under MySQL or MSSQL.
>>>
>>> Do you plan to introduce MONTH and YEAR functions?.
>>>
>>>
>>> Also, the function strftime('%m', fecha) does not solve the issue:
>>>
>>> SELECT SUM( NINGRESO ) AS ndev FROM APUNTES WHERE strftime('%m', fecha) = 1
>>>
>>>
>>> Please, any idea of how to solve it.
>>>
>>>
>>> Thank you. Best regards,
>>>
>>> Lucas de Beltran
>>> Caritas España
>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> --
>> ASCII ribbon campaign ( )
>>  against HTML e-mail   X
>>  www.asciiribbon.org  / \
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com



--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users