Android Question SQL Convert String to Date

Status
Not open for further replies.

metrick

Active Member
Licensed User
Longtime User
I have data that I would like to retrieve from db using the following sql select statement;
B4X: 
"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]"
 
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
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)
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
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
 
Last edited:
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
klaus said:
I tryed your suggestion but got an error.
Click to expand...
Hi klaus:
Did you try the query something like:
B4X: 
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"
 
Upvote 0
S

seanfogg

Member
Licensed User
Longtime User
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
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
@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.
 
Upvote 0

Didier9

Well-Known Member
Licensed User
Longtime User
DonManfred said:
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.
Click to expand...

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.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
klaus said:
My concern was that in your post you have the concatenate operator between quotes which gave me the error.
Click to expand...
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:

B4X: 
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)
 
Upvote 0

tcgoh

Active Member
Licensed User
Longtime User
Hi,

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

Thanks
 
Upvote 0
E

emexes

Expert
Licensed User
tcgoh said:
My date string in my datebase is in "02Feb20" format, anyone knows how to convert SQL format?
Click to expand...
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).
 
Upvote 0
Status
Not open for further replies.

Similar Threads

Bug? NumberFormatException on a string date
Replies
2
Views
892
udg
F
  • Question
Android Question Replace variables in string
Replies
13
Views
2K
faholet540
F
N
  • Question
Android Question java.lang.NumberFormatException: For input string!
Replies
2
Views
3K
NewDD
N
  • Question
Android Question Parse String to Ticks
Replies
2
Views
3K
hibrid0
S
  • Question
Android Question JSON string to Date conversion
Replies
1
Views
2K
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…