System.Data.SQLite

Hello,

I have a datetime field (mydate) in my table (mytable), and I want to get all different year from this table.

Here is the query which works in MySQL :

string queryString = "Select distinct year(mydate) as year from mytable"

I want to make the same query using SQLite

I have try this :

string queryString = @"Select distinct strftime(""%Y"", mydate) as year from mytable"

But the result is always an empty string.

This following exemple works :

 string queryString = @"Select distinct strftime(""%Y"", 'now') as year"  --> return "2007"

 string queryString = @"Select distinct strftime(""%Y"", '2001-01-01 19:05:04'') as year"  --> return "2001"

I think, my problem is to specify correctly the field 'mydate'. I have tried :

- mydate 

- 'mydate' 

- [mydate]

without success.

What is wrong ?

Is there a other way to make this query ? 

Thanks in advance

Olivier 

Not really sure what's going wrong without looking at your database.  How are the dates in your database formatted?  If they don't use ISO8601 formatting, then the strftime() function won't work properly.

Robert