CODEWITHSUNDEEP
sqlitedate
CrazyProgrammer
CrazyProgrammer

Reputation: 49

how to extract only month from date which is in sqlite database?

I want to fetch few data that occur in particular month. For example, I need to choose all the names of employees who joined in July(irrespective of date). What is the query to choose particular month field alone from date field from database ? How do I compare the month field in database(stored in date of format mm/dd/yyyy) and the user given value of month. I'm using sqlite3 database and date field is set to text. Thanks in advance.

Upvotes: 1

Views: 2139

Answers (2)

Nikhil S Marathe
Nikhil S Marathe

Reputation: 771

I Resolving this using this code in SQLITE.

If You have dat like this format : "2021-12-23 12:33:01" then convert it in strftime() format function.

ex.-> Select strftime('%d/%m/%Y',EntryDate) as Date  from table_name;

then output comes in

     "2021-12-23 12:33:01"  to "23-12-2021"

and then fire this query to get MONTH NAME from MONTH NUMBER in SQLITE

-> using case we can fetch it.


 Select strftime('%d/%m/%Y',checkInDate) as Date, 
  case strftime('%m', checkInDate)  when '01' then 'JAN' 
  when '02' then 'FEB' when '03' then 'MAR' when '04' then 'APR' when '05' then 'MAY' when '06' then 'JUN' 
  when '07' then 'JUL' when '08' then 'AUG' when '09' then 'SEP' when '10' then 'OCT'  
  when '11' then 'NOV' when '12' then 'DEC' else '' end as Month   from AttendanceTable

☻♥ Done Keep Code.

Upvotes: 0

user1907906
user1907906

Reputation:

SQLite only has a small set of date and time functions. You can use them like this:

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 (f1 string);
INSERT INTO "t1" VALUES('03/31/1970');
COMMIT;

sqlite> select substr(f1, 7) || '-' || substr(f1, 0, 3) || '-' || substr(f1, 4, 2) from t1;
1970-03-31

sqlite> select strftime("%m", substr(f1, 7) || '-' || substr(f1, 0, 3) || '-' || substr(f1, 4, 2)) from t1;
03

Upvotes: 2

Related Questions