0

i have this in my Sqllite Database query, i want ... 

select BoardingDate, strftime('%Y', date(BoardingDate)) from Hostels;

but in the output it renders BoardingDate correctly and the output

BoardingDate        Month 
-------------------------
8/1/2007 0:00:00    null
3/1/2008 0:00:00    null
8/1/2008 0:00:00    null
3/1/2009 0:00:00    null

These is the current behaviour of the queries 

 1. strftime('%Y', date('now'))         - outputs correctly
 2. strftime('%Y', date('2020-03-06'))  - it works also
 3. strftime('%Y', date(BoardingDate))  - Gives an error, it doesnt render at all.. 
 4. strftime('%Y', date(Hostels.BoardingDate))  - The same error...
 5. strftime('%Y', datetime(BoardingDate))  - Gives an error,
 6. strftime('%Y', datetime(Hostels.BoardingDate))  - doesnt work....

it prints the string, i dont know why. its working this way. i'm trying to calculate the date using the julianday() function but thats a longer approach i'm just confused right now. thanks.

This is the error i get in the Message Panel

select BoardingDate, strftime('%Y',BoardingDate)
> no such column: BoardingDate
> Time: 0s

but the first column render correctly, but the function column returns this error I tried following the solutions on these pages but to no avail

get-month-from-datetime-in-sqlite

sqlite-convert-string-to-date

i have tried other functions from this documentation lang_datefunc.html but i still get the same error, when i try to pass a column reference as a parameter in a function

Ande Caleb
  • 1,163
  • 1
  • 14
  • 35
  • 2
    Change the format of your dates to YYYY-MM-DD and it will work. – forpas Mar 13 '20 at 16:15
  • @forpas but how do i convert `8/1/2007` to `08/01/2017` within a query string? is there a way out? i'd be glad, if you could help with code. because i'm pulling from an already existing database. – Ande Caleb Mar 13 '20 at 16:30
  • A **very** complicated way with string functions. – forpas Mar 13 '20 at 16:34
  • What I suggest is to import the table in a database that is more flexible with dates and strings (like MySql) and do the conversions there and reimport back to SQLite. – forpas Mar 13 '20 at 16:37

1 Answers1

0

This "how do i convert 8/1/2007 to 08/01/2017 within a query string" won't help, since that is not a valid sqlite time string. And if MySql is not an option (which it is not for me), you would have to do the "complicated string functions". Which can be fairly straight forward once they're broken down.

The year is easy, it is substr(adate,-12,4) assuming the time component only has one digit for hour (-13 otherwise). And if you really only want the year (based on '%Y') then bob's your uncle.

The month, likewise, is easy. Exploiting CAST which will convert text to an integer until the first non-numeric character, and using printf to 0 pad, this printf('%02i',CAST (adate as int)) will give the month.

The day is a little more typing, because you need the part of the string after the first '/'. This printf('%02i',CAST (substr(adate,instr(adate,'/') + 1) as int)) will do the trick.

All that's left is to concat them all together with some - in between, and voilà, it's a valid sqlite date.

DinoCoderSaurus
  • 6,110
  • 2
  • 10
  • 15