How to Format/Separate Date and Time from a sqlite datetime field

Question

I have the table Pitches with the following fields:

CREATE TABLE [LANCAMENTO](
  [ID] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL UNIQUE, 
  [VALOR] DECIMAL(8, 2), 
  [DATE_BUY] DATETIME, 
  [DESCRIPTION] VARCHAR(150), 
  [TYPE_RELEASE] VARCHAR(1), 
  [ORGANIZATION] VARCHAR(50), 
  [ID_USUARIO] INTEGER NOT NULL REFERENCES USUARIOS([ID]));

I want to know how to catch only Time or the Date country DATE_BUY.

Answer 1

According to the sqlite documentation the functions date(), time() and datetime() may be written in terms of function strftime(). So, to do what you want, the query is as follows:

SELECT
   strftime('%Y-%m-%d',date_buy) as data,
   strftime('%H:%M:%S', date_buy) as hora
FROM
   lancamento

In date you will have the format: yyyy-mm-dd and in time the format hh:mm:ss.

Answer 2

%Y is for years %m is for months and %d is for Days %H is for Hours %M is for Minutes and %S is for Seconds

SELECT strftime('%Y-%m-%d',date) as data,
       strftime('%H:%M:%S', date) as hora
FROM lancamento