Android Question Is it safe to do like this?

Alex_197 · Jan 2, 2021

Hi all and happy 2021.

I'm run into the problem (yes, I know, my fault).

The problem is that I saved date into my table like this 05/05/2020. For the whole year it was OK but this morning I realized that I did it wrong.

When I run a query like this

B4X:

select * from mytable where jobdate between '12/31/2020' and '1/1/2021'

I got nothing and the app shows me wrong info.

So my question is - is it safe to change the query to this

B4X:

select * from mytable
where julianday((substr(NN.JobDate,7,4) || '-' || substr(NN.JobDate,1,2) ||'-' || substr(NN.JobDate,4,2) || 'T00:00:00'))  between
julianday(DATE('now','-1 day',  'localtime')) and julianday (date('now','localtime') )

Or it's better to use

B4X:

dim TickFrom as Long=DateTime.Dateparse(strDateFrom)
dim TickTo as Long=DateTime.Dateparse(strDateTo)

and pass TickFrom and TickTo as parameters

B4X:

select * from mytable
where julianday((substr(NN.JobDate,7,4) || '-' || substr(NN.JobDate,1,2) ||'-' || substr(NN.JobDate,4,2) || 'T00:00:00'))  between
? and ?

Thanks.

Mahares · Jan 2, 2021

Alex_197 said:
he problem is that I saved date into my table like this 05/05/2020.

I have had cases where the date is saved as: dd/MM/yyyy. Here is how I handled it in queries and it delivered. For instance, If I am looking for data between
12/31/2020 and 01/01/2021. The 2 dates as parameters can easily be converted to yyyyMMdd

B4X:

Dim strQuery As String =$"SELECT *,
     substr(jobdate, 7,4) || substr(jobdate, 4,2) || substr(jobdate, 1,2) AS jdate
     FROM mytable WHERE jdate BETWEEN ? AND ?
    "$
    Dim rs As ResultSet= Starter.SQL1.ExecQuery2( strQuery, Array As String ("20201231", "20210101"))

Alex_197 · Jan 2, 2021

Mahares said:
I have had cases where the date is saved as: dd/MM/yyyy. Here is how I handled it in queries and it delivered. For instance, If I am looking for data between
12/31/2020 and 01/01/2021. The 2 dates as parameters can easily be converted to yyyyMMdd

B4X:

Dim strQuery As String =$"SELECT *, substr(jobdate, 7,4) || substr(jobdate, 4,2) || substr(jobdate, 1,2) AS jdate FROM mytable WHERE jdate BETWEEN ? AND ? "$ Dim rs As ResultSet= Starter.SQL1.ExecQuery2( strQuery, Array As String ("20201231", "20210101"))

Thanks for your replay.

Why you don't pass dates as ticks?

Mahares · Jan 2, 2021

Alex_197 said:
Why you don't pass dates as ticks?

If it works for you, that is fine too of course. I simply prefer to show dates as dates to make it easy for the user and developer. It is a lot easier to tell that 01/01/2021 is a date than a tick value 1609477200000

Mahares · Jan 3, 2021

Alex_197 said:
Why you don't pass dates as ticks?

I still can’t see how you are currently storing your dates as date string dd/MM/yyyy in the table, then use the Julianday to query the data with parameters in ticks. The julianday function output and tick parameters do not match., But if you store your dates as ticks in the table then you can query your data with parameters as ticks and convert your ticks to string dates in the output.

Android Question Is it safe to do like this?

Alex_197

Well-Known Member

Mahares

Expert

Alex_197

Well-Known Member

Mahares

Expert

Mahares

Expert

Similar Threads

Android Question Is it safe to do like this?

Alex_197

Well-Known Member

Mahares

Expert

Alex_197

Well-Known Member

Mahares

Expert

Mahares

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency