How to Query in SQLite for Different Date Format

how to query in sqlite for different date format

strftime works like this:

sqlite> select strftime('%d-%m-%Y %H:%M:%S', datetime('now'));
2012-09-13 12:42:56

If you want to use it in a WHERE clause, replace datetime('now') with the datetime you want to match in the YYYY-mm-dd HH:MM:SS format.

Example:

SELECT *
  FROM t
 WHERE c = strftime('%d-%m-%Y %H:%M:%S', '2012-09-13 12:44:22');
--                   dd-mm-YYYY HH:MM:SS  YYYY-mm-dd HH:MM:SS

How to change date format in sqlite database

try this query

select strftime('%d/%m/%Y',datetime(substr(t_date, 7, 4) || '-' || substr(t_date, 4, 2) || '-' || substr(t_date, 1, 2))) from party_trans;

How can I fix wrong date format in SQLite

Update your dates to the only valid for SQLite date format which is YYYY-MM-DD:

update tablename
set date = substr(date, -4) || '-' || 
           substr('00' || (date + 0), -2, 2) || '-' ||
           substr('00' || (substr(date, instr(date, '/') + 1) + 0), -2, 2);

See the demo.

Results:

| ID  | Date       |
| --- | ---------- |
| 1   | 2019-09-02 |
| 2   | 2020-02-20 |

Now you can set the conditions like:

Date BETWEEN '2019-02-05' AND '2020-02-20'

If you do this change then you can use the function strftime() in select statements to return the dates in any format that you want:

SELECT strftime('%m/%d/%Y', date) date FROM Table

If you don't change the format of date column then every time you need to compare dates you will have to transform the value with the expression used in the UPDATE statement, and this is the worst choice that you could make.

How to format DateTime column in query for sqlite?

First, 'CREATETS' is a string literal and not a column name. If you need to refer to a column, remove the '' quotes.

Second, 21-03-2011 12:00:00.000000 is not a time string as understood by sqlite and attempting to convert it using datetime functions will result in null.

Technically it is possible to convert the your datetime values to a time string understood by sqlite using SUBSTR() to extract parts of the value and reorder the fields.

However, SQL is not the right place to put your presentation/formatting code in. Do that in your Java code instead. Also, it would be easier if you just stored timestamps in a "raw" format such as unix timestamp (seconds since epoch) or Java milliseconds timestamp instead of formatted strings.

How to change date format in SQLite

You can concatenate each of the day, month and year with implicit conversions to integers so that leading 0s are stripped off:

(date + 0) || '/' || (substr(date, 4, 2) + 0) || '/' || substr(date, -2)

change date to the name of your column.

See the demo.

SQLite selecting date, with different date-format

date is a string literal and needs to be put in single quotes. Or better yet, use ? placeholder and bind arguments.

There's no syntax error since something like 2014-11-27 is a valid expression that evaluates to the integer 1976.

sqlite select with condition on date

Some thing like this could be used:

select dateofbirth from customer Where DateofBirth  BETWEEN date('1004-01-01') AND date('1980-12-31');  
select dateofbirth from customer where date(dateofbirth)>date('1980-12-01');
select * from customer where date(dateofbirth) < date('now','-30 years');

If you are using Sqlite V3.7.12 or greater

Dont use date(dateofbirth) just use dateofbirth. So your query would look like this:

select * from customer where dateofbirth < date('now','-30 years');

Convert Date format in SQLite Database

If you have only 2 different date formats, you can reformat them by parsing differenly. Follow @MatBailie's suggestion and use a format supported by Sqlite's functions, i used YYYY-MM-DD

I decoded MM/DD/YYYY format by finding parts of a date. And than, i decoded DD-Month Abbr. - YY format separately for each Month. 

For MM/DD/YYYY format

update dates set theDate = substr(theDate, 7, 4 ) 
                        || '-' || substr(theDate, 1, 2 ) || '-'
                        || substr(theDate, 4, 2 )
where instr(theDate, '/') > 0; 

For DD-Month Abbr. - YY format

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-01-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Jan-') > 0;                         

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-02-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Feb-') > 0;  

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-03-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Mar-') > 0;  

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-04-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Apr-') > 0;  

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-05-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-May-') > 0; 

update dates set theDate = substr(theDate, 9, 4 ) 
                        || '-06-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-June-') > 0; 

update dates set theDate = substr(theDate, 9, 4 ) 
                        || '-07-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-July-') > 0; 

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-08-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Aug-') > 0; 

update dates set theDate = substr(theDate, 9, 4 ) 
                        || '-09-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Sept-') > 0; 

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-10-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Oct-') > 0; 

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-11-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Nov-') > 0; 

update dates set theDate = substr(theDate, 8, 4 ) 
                        || '-12-'
                        || substr(theDate, 1, 2 )
where instr(theDate, '-Dec-') > 0; 

Tested on sqlfiddle.com

---

Related Topics