Android Question SQL Convert String to Date

[metrick]

I have data that I would like to retrieve from db using the following sql select statement;

"SELECT [Mydate], ITEM, DESC FROM MyTable WHERE [Mydate] BETWEEN '10/10/2016' AND '12/31/2016' Order by [Mydate]"

'[Mydate] is date store as string eg. "10/10/2016"
'If my select sql has the same year,I was able to retrieve data eg.
'however if the select statement has difference year , the statement return no data.
'eg. below.
"SELECT [Mydate], ITEM, DESC FROM MyTable WHERE [Mydate] BETWEEN '10/10/2016' AND '01/10/2017' Order by [Mydate]"

 

[DonManfred]

SQLLite requires dates to be in YYYY-MM-DD format. Since the data in your database and the string in your query isn't in that format, it is probably treating your "dates" as strings.

 

[Mahares]

As mentioned by Manfred, unless you store the date as: YYYY-MM-DD or YYYYMMDD or ticks, it will be tough to display the results. But if you have no choice but to use your format, then use the SUBSTR(X,Y,Z) function: for instance: for 12/31/2016 you can do:
Substr(MyDate, 7,4) '||' Substr(MyDate, 1,2) '||' Substr(MyDate, 4,2)

 

[klaus]

Hi Mahares,
I tryed your suggestion but got an error.
To convert 12/31/2016 to 2016/12/31 following code works:
substr(MyDate, 7,4) || '/' || substr(MyDate, 1,2) || '/' || substr(MyDate, 4,2) AS Date
and
WHERE Date BETWEEN '2016/10/10' AND '2017/01/10' ORDER BY Date

 

[metrick]

Found a link. Not at home now, and unable to try it.
http://stackoverflow.com/questions/4428795/sqlite-convert-string-to-date

 

[Mahares]

I tryed your suggestion but got an error.

Hi klaus:
Did you try the query something like:

Dim MyQuery As String
    MyQuery= "SELECT Mydate, ITEM FROM MyTable WHERE (Substr(MyDate, 7,4) || Substr(MyDate, 1,2) || Substr(MyDate, 4,2)) AS DATE" _
    & " BETWEEN '20161010' AND '20161231' ORDER BY DATE"

 

[seanfogg]

Not sure about sqllite but you can try the CDATE("dd/mm/yyyy") function or encase the date in hashes #dd/mm/yyyy# - must be applied to all dates in the query

 

[klaus]

@Mahares
My concern was that in your post you have the concatenate operator between quotes which gave me the error.
Substr(MyDate, 7,4) '||' Substr(MyDate, 1,2) '||' Substr(MyDate, 4,2)
instead of
Substr(MyDate, 7,4) || Substr(MyDate, 1,2) || Substr(MyDate, 4,2)

Of course, your query in post#6 works because you don't have the quotes.

 

[Didier9]

SQLLite requires dates to be in YYYY-MM-DD format. Since the data in your database and the string in your query isn't in that format, it is probably treating your "dates" as strings.

Yes, having just gone through quite a bit of work to get date formats fixed in my project, that can be head scratching.
Having some experience abroad, I have tried to standardize the date format in my projects to "dd-MMM-yyyy" (or "dd MMM yyyy") since that is just about the only format that is most likely to be universally recognized in the world without any ambiguity (ask people in 3 or 4 European countries what does 2017/03/02 mean).
Since SQLite cannot deal with this format directly, I keep the date format in the database as ticks and I do all the conversions outside of SQLite.

 

[Mahares]

My concern was that in your post you have the concatenate operator between quotes which gave me the error.

Hi klaus:
I am sorry when I posted I did not have a PC to test my little, but now that I tested it, here is exactly how it should be. Tested now and works:
of course you use you own data and column names:

Dim MyQuery As String
    MyQuery= "SELECT Mydate, col2, col3, (Substr(MyDate, 7,4) || Substr(MyDate, 1,2) || Substr(MyDate, 4,2)) AS DATE" _
    & " FROM table1 WHERE DATE BETWEEN '20170217' AND '20170220' ORDER BY DATE"
    cursor1=SQL1.ExecQuery(MyQuery)

 

[klaus]

The code I posted works also, I tested it, but it's bit more complex with the '/' character in between the substrings.

 

[tcgoh]

Hi,

My date string in my datebase is in "02Feb20" format, anyone knows how to convert SQL format?

Thanks

 

[emexes]

my datebase is

Always makes me smile when typo is a better fit than the intended word ✌

 

[emexes]

My date string in my datebase is in "02Feb20" format, anyone knows how to convert SQL format?

You can do it yourself with straight string operations. Two major issues are:

1/ what to do about century, eg is 02Feb95 supposed to be 1995 or 2095?

2/ converting 3-letter month to a month number 1..12. Easiest way is "xxJanFebMarAprMayJunJulAugSepOctNovDec".IndexOf("Feb") will return position of the month within the string of all 12 months, and then divide that position by 3 (the leading xx is to pad the position to be a multiple of 3).

 

[Erel]

My date string in my datebase is in "02Feb20" format, anyone knows how to convert SQL format?

Please don't post in old threads. Always start a new thread for your question. You can post a link to a similar thread.