---
title: "Same queries displaying displaying different date in results?"  
description: "Same queries displaying displaying different date in results?"  
author: "Anonymous User"  
published: 2015-09-30  
updated: 2023-05-27  
canonical: https://www.mindstick.com/forum/33482/same-queries-displaying-displaying-different-date-in-results  
category: "sqlite"  
tags: ["database", "sqlite", "sqlite3"]  
reading_time: 3 minutes  

---

# Same queries displaying displaying different date in results?

I have a [problem](https://yourviews.mindstick.com/view/81399/tackling-the-problem-of-unemployment-during-corona-pandemic) with different [results](https://yourviews.mindstick.com/story/4497/usage-of-baking-soda-magical-results) from [two tables](https://www.mindstick.com/forum/159646/how-to-join-two-tables-in-oracle-to-get-single-line-results), both tables have three rows but different date [formats](https://www.mindstick.com/forum/160705/explain-the-concept-of-datetime-formats-in-c-sharp) here is the [code](https://yourviews.mindstick.com/view/85458/alan-turing-the-mastermind-behind-cracking-the-enigma-code-during-world-war-ii):

```
sqlite> create table table1 (date text);sqlite> create table table2 (date text);
```

Three rows with [dates](https://yourviews.mindstick.com/story/1513/7-zodiac-signs-astrology-dates-meanings-amp-compatibility) but the different formats:

```
sqlite> select * from table1;28.09.201528.08.201529.08.2015
```

```
sqlite> select * from table2;2015-09-282015-08-282015-08-29
```

My [current date](https://www.mindstick.com/forum/323/find-the-day-name-and-month-name-from-current-date) is:

```
sqlite> select date('now');2015-09-29
```

Why do I have a different results for the next similar [queries](https://www.mindstick.com/forum/33678/sub-queries-in-sql-server)?

```
sqlite> select * from table1 where date < strftime('%d.%m.%Y', 'now',                                                  '-1 day');28.08.2015
```

```
sqlite> select * from table2 where date < strftime('%Y-%m-%d', 'now',                                                  '-1 day');2015-08-282015-08-29
```

Why first [query](https://www.mindstick.com/blog/202/sub-query-in-sqlserver) don't returns '29.08.2015' too?

## Replies

### Reply by Aryan Kumar

If you are experiencing different dates in the results of the same queries, there could be a few potential reasons for this:

**1. Timezone differences:**\
- Check if there are timezone differences between the environment or system where the queries are executed. The dates stored in the database may be in a specific timezone, and the results you see might be adjusted based on the timezone settings of the environment where the queries are executed.\
- Ensure that the timezone settings are consistent across the systems involved to obtain consistent results.

**2. Date formatting:**\
- Verify that the dates in the database are stored in the desired format and are correctly interpreted during the query execution. Different date formats or variations in how dates are stored and retrieved can lead to inconsistent results.\
- Review the date formatting functions or methods being used in the queries to ensure they align with the format of the dates stored in the database.

**3. Data inconsistencies:**\
- Check if there are any inconsistencies or variations in the data itself. It's possible that the dates in the database have been modified or updated, leading to different results in subsequent queries.\
- Review the data modification history and any processes that might have affected the dates in the database.

**4. Timestamp precision:**\
- If the dates involve timestamps with high precision, such as including milliseconds or microseconds, slight differences in the timestamp values can lead to different results in subsequent queries.\
- Ensure that the precision and rounding of timestamps are consistent across the queries and the data stored in the database.

**5. Caching or data retrieval mechanisms:**\
- If you are using caching mechanisms or other data retrieval layers, verify that the cache is not interfering with the query results. In some cases, cached data might be returned instead of querying the database directly, leading to different dates being displayed.\
- Check if there are any caching mechanisms in place and evaluate their configurations and expiration policies.

By investigating these factors, you can narrow down the potential causes of inconsistent dates in query results. It's important to ensure consistent handling of timezones, date formatting, data integrity, and caching mechanisms to obtain reliable and consistent results across queries.

### Reply by Tarun Kumar

SQLite has no date type. When you do date < strftime(...) you're doing a string comparison. The ISO 8601 date format like 2015-08-28 will compare as dates when compared as strings. 28.08.2015 will not, it will weigh the day first, then the month, then the year.

strftime only understands a limited set of formats (see "Time Strings") and 28.09.2015 isn't one of them. Either store all dates in ISO 8601 format, or follow the answers to this question cover how to compare dates in SQLite.


---

Original Source: https://www.mindstick.com/forum/33482/same-queries-displaying-displaying-different-date-in-results

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.