Datetime conversion in SQLite

Question

I am using SQLite database, where I need some help

select Value from SdVarTbl where Name='DateCreated.Sd'

This query returns a single row and single columns. Which is a varchar type in database.

And another query

select ComputerName, LastCheckIn from ClientB

which returns

My query:
I want to show another column say "Difference", which will print the date difference between and first query and each value in the second query.

HOW to achieve that. I have tried to use datetime, strftime function, but couldn't succeed till now.

select ComputerName, datetime(LastCheckIn) as LastCheckIn, (select strftime(Value) from SdVarTbl where Name='DateCreated.Sd') - datetime(LastCheckIn) as Difference from ClientB — AMIT SHELKE
My above query didn't work, I don't know where it is getting wrong. — AMIT SHELKE

bwt bwt · Accepted Answer · 2018-06-26T11:39:23

I assume you want the difference as a number of days, so I suggest using the julianday() function. It's result is the number of days (since noon in Greenwich on November 24, 4714 B.C, but this doesn't really matter if you use it to calculate differences) :

select c.ComputerName,
  c.LastCheckIn,
  julianday(c.LastCheckIn) - julianday(v.value) as diff_days
from ClientB c
join SdVarTbl v
  on v.Name='DateCreated.Sd'

Datetime conversion in SQLite

1 Answers