Android Question Is it safe to do like this?

[Alex_197]

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

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

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

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

and pass TickFrom and TickTo as parameters

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]

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

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]

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

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]

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]

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.