Android Question Want Substr for hour/min/second/millisecond

[Pravee7094]

Hi all,

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)

From @Mahares

I tested the above code. And its perfectly works for (year/month/date) format.
And I also want (hour/min/second/millisecond) this format.

Anyone Help!

Thanks
Praveen

 

[Albert Kallal]

This being sqlite?

You want to do it this way:

SELECT  [Start time], strftime('%Y',[Start Time]) as Myyear ,
strftime('%m',[Start Time]) as MyMonth,
strftime('%d',[Start Time]) as MyDay,
strftime('%H',[Start Time]) as MyHour,
strftime('%M',[Start Time]) as MyMinutes,
strftime('%s',[Start Time]) as MySeconds

from InvoiceDetail where [Start time] is not NULL ORDER BY [Start Time] DESC

Output:

And doing this will result in numbers etc. - you can sort on them.

EDIT:
I see you also wanted mili-seconds. That would be a S. The codes? I have this page booked marked - I often use it, since it ALSO applies to the built in
" DateTime.DateFormat = sFormat" also follows that java reference, and thus works in B4A + DateTime.DateFormat = "whatever".

Note that in the screen cap, I have a S in palce of a lower case s - so that would be a "bug" in this post ;-)


https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Edit#2:
If you REALLY need the mili-seconds from above? Their not a single function. But this works:

​

SELECT  [Start time], strftime('%Y',[Start Time]) as Myyear ,
strftime('%m',[Start Time]) as MyMonth,
strftime('%d',[Start Time]) as MyDay,
strftime('%H',[Start Time]) as MyHour,
strftime('%M',[Start Time]) as MyMinutes,
strftime('%S',[Start Time]) as MySeconds,
strftime('%f',[Start Time]) as MySecondsWithMili,

substr(strftime('%f',[Start Time]),4) as MyMilSeconds

from InvoiceDetail where [Start time] is not NULL 
AND [Start Time] BETWEEN '2020-02-01' AND '2020-02-28'   ORDER BY [Start Time] DESC

Output:

I never pulled mili-seconds. And I noticed that the list/link I provided is not complete.
(if anyone has a nice "list" of the valid time pluckier settings for sqlite - I'm open to such a link - that list did not show "f" that I used in above).

Regards,
Albert D. Kallal
Edmonton, Alberta Canada