> Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren’t used in SQL, but that can vary from database to database.
You can rely on PostgreSQL docs regarding what the “SQL standard requires”:
> **[8.5. Date/Time Types](https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT)**
>
> 8.5. Date/Time Types # 8.5.1. Date/Time Input 8.5.2. Date/Time Output 8.5.3. Time Zones 8.5.4. Interval Input 8.5.5. Interval Output PostgreSQL supports …
> PostgreSQL is more flexible in handling date/time input than the SQL standard requires. See [Appendix B](https://www.postgresql.org/docs/current/datetime-appendix.html) for the exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones.
> Remember that any date or time literal input needs to be enclosed in single quotes, like text strings. Refer to [Section 4.1.2.7](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC) for more information. SQL requires the following syntax
>
> [not shown, didn’t copy-paste correctly]
>
> where _`p`_ is an optional precision specification
I think it means requires with `date` (or other options) as I showed above.
> Use one of the SQL functions instead in such contexts. For example, `CURRENT_DATE + 1` is safer than `'tomorrow'::date`.
> [@Dan](#):
>
> `WHERE date >= '2024-10-03'` (with single quote).
Yes, I’m getting rusty. Also even if double quotes allowed, then you would need to escape.
In other context not allowed:
> **[Breaking Down SQL Syntax Guide to Using Quotes](https://dev.to/salmazz/breaking-down-sql-syntax-guide-to-using-quotes-8ic)**
>
> In SQL, the use of quotes can vary based on the context and the specific SQL database system you are...
> For instance, `"Customers"` or `"Order ID"`. However, not all SQL databases require or allow double quotes for identifiers. For example, MySQL often uses backticks (`) instead of double quotes for this purpose.
> …
>
> ### Using Double Quotes for Identifiers
>
> - **PostgreSQL / Standard SQL**
>
> …
> Double quotes are used because PostgreSQL adheres closely to the SQL standard, which recommends double quotes for identifiers.
> …
> Always remember to check the documentation for the specific SQL database you are using, as these conventions can vary. For example, what works in PostgreSQL might not work exactly the same way in MySQL or Microsoft SQL Server.
> **[8.5. Date/Time Types](https://www.postgresql.org/docs/current/datatype-datetime.html)**
>
> 8.5. Date/Time Types # 8.5.1. Date/Time Input 8.5.2. Date/Time Output 8.5.3. Time Zones 8.5.4. Interval Input 8.5.5. Interval Output PostgreSQL supports …
> The SQL standard differentiates `timestamp without time zone` and `timestamp with time zone` literals by the presence of a “+” or “-” symbol and time zone offset after the time. Hence, according to the standard,
>
> TIMESTAMP ‘2004-10-19 10:23:54’
>
> is a `timestamp without time zone`, while
>
> TIMESTAMP ‘2004-10-19 10:23:54+02’
>
> is a `timestamp with time zone`. PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as `timestamp without time zone`. To ensure that a literal is treated as `timestamp with time zone`, give it the correct explicit type:
>
> TIMESTAMP WITH TIME ZONE ‘2004-10-19 10:23:54+02’
> **[9.9. Date/Time Functions and Operators](https://www.postgresql.org/docs/current/functions-datetime.html)**
>
> 9.9. Date/Time Functions and Operators # 9.9.1. EXTRACT, date\_part 9.9.2. date\_trunc 9.9.3. date\_bin 9.9.4. AT TIME ZONE and AT LOCAL 9.9.5. …
> PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:
>
> > transaction\_timestamp()
> > statement\_timestamp()
> > clock\_timestamp()
> > timeofday()
> > now()
---
### Author:  [@stephancb](https://discourse.julialang.org/u/stephancb)
#### Post date: [November 12, 2024, 3:31am UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/10 "2024-11-12T03:31:25Z")
```julia
SELECT id, category, date FROM todos WHERE date BETWEEN ‘2024-10-03’ AND ‘2024-10-27’ ORDER BY date;
```
should work.
[SQLite](https://www.sqlite.org/lang_datefunc.html) does not have a date type but can store dates as TEXT (strings). The `yyyy-mm-dd` format sorts correctly, therefore the above SQL works provided that the dates are stored in this format.
Because `2024-10-03` is an algebraic expression,
```julia
sqlite> SELECT 2024-10-03;
2011
```
```julia
julia> df = DBInterface.execute(db, “SELECT id, category, date FROM todos WHERE date >= 2024-10-03 ORDER BY date” ) |> DataFrame
```
does not work.
Because `date` is not a type in SQLite, a default cast to INTEGER is done in
```julia
sqlite> select cast('2024-10-05' as date);
2024
```
and the cast in
> [@andreh](#):
>
> julia\> DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE (cast(date as date) BETWEEN ‘2024-10-03’ AND ‘2024-10-27’) ORDER BY date " ) |\> DataFrame
will not give the expected result.
---
### Author:  [@andreh](https://discourse.julialang.org/u/andreh)
#### Post date: [November 12, 2024, 5:32pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/11 "2024-11-12T17:32:57Z")
@Palli give me some time to check it out and read your comments
Thank you, I will update you
---
### Author:  [@andreh](https://discourse.julialang.org/u/andreh)
#### Post date: [November 12, 2024, 5:56pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/12 "2024-11-12T17:56:41Z")
> [@Palli](#):
>
> I could try changing to: `SQLWhereExpression("date >= '?' AND date <= '?'", startdate, enddate)` or I think you want to change even further to `SQLWhereExpression("date BETWEEN '?' AND '?'", startdate, enddate)`
I don’t think that would be the option , I get the error:
---
### Author:  [@andreh](https://discourse.julialang.org/u/andreh)
#### Post date: [November 12, 2024, 6:48pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/13 "2024-11-12T18:48:15Z")
yes, is doing an arithmetic operation, what would be then the option to retrieve specific date values with SQLite ?
---
### Author:  [@andreh](https://discourse.julialang.org/u/andreh)
#### Post date: [November 12, 2024, 7:31pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/14 "2024-11-12T19:31:05Z")
@Palli
So far, no updates, the date is treated as an algebraic expression as @stephancb mentioned:
> Because `2024-10-03` is an algebraic expression,
```julia
sqlite> SELECT 2024-10-03;
2011
```
So far, I don’t know yet how to handle it, even in the SQLWhereExpression … I think is also doing an arithmetic operation
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [November 12, 2024, 10:56pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/15 "2024-11-12T22:56:16Z")
have you tried running the same query with duckdb? duckdb will load/handle the sqlite db no problem
---
### Author:  [@stephancb](https://discourse.julialang.org/u/stephancb)
#### Post date: [November 13, 2024, 12:58am UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/16 "2024-11-13T00:58:12Z")
Did you try
```julia
julia> DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE date BETWEEN ‘2024-10-03’ AND ‘2024-10-27’ ORDER BY date " ) |> DataFrame
```
i.e. without the incorrect CAST and the dates as strings within single quotes '?
---
### Author:  [@andreh](https://discourse.julialang.org/u/andreh)
#### Post date: [November 13, 2024, 10:52pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/17 "2024-11-13T22:52:52Z")
yes
```julia
julia> DBInterface.execute(db, "SELECT * FROM todos WHERE date >= '2024-10-03'") |> DataFrame
70×7 DataFrame
Row │ id todo completed user_id category date duration
│ Int64 String Int64 Int64 String Date Int64
─────┼────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 25 Architecto natus quam laudantium… 1 4 family 2024-08-10 13
2 │ 72 Assumenda distinctio consequatur. 1 4 other 2024-08-13 207
3 │ 24 Voluptatibus. 0 4 other 2024-08-14 206
4 │ 47 Facilis a sint aliquam at maxime. 0 3 work 2024-08-14 67
5 │ 35 Cupiditate dolorem odio id aut. 0 4 learning 2024-08-17 179
6 │ 42 Vitae eligendi commodi aut omnis. 0 3 accounting 2024-08-17 98
7 │ 52 Eius excepturi nulla deserunt eu… 1 4 shopping 2024-08-17 153
8 │ 6 Voluptas eius laboriosam suscipi… 0 4 family 2024-08-18 183
9 │ 9 Commodi nam enim. 1 4 work 2024-08-18 58
10 │ 74 Dignissimos dolores molestiae no… 0 4 work 2024-08-19 23
11 │ 61 Cumque sint. 1 3 accounting 2024-08-21 191
12 │ 63 Veritatis molestias rerum fuga q… 1 4 hobby 2024-08-21 112
13 │ 50 Officiis ab ipsum quae. 1 4 errands 2024-08-22 203
14 │ 39 Magnam. 1 3 accounting 2024-08-24 230
15 │ 20 Et veniam. 1 3 shopping 2024-08-25 42
16 │ 31 Est sint autem voluptatem aut. 0 4 accounting 2024-08-26 145
17 │ 66 Quaerat qui earum est voluptatum… 0 4 accounting 2024-08-26 207
18 │ 33 Asperiores corrupti in ut nam. 1 4 hobby 2024-08-27 46
19 │ 60 Esse quos aspernatur est. 1 3 hobby 2024-08-28 39
⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮
53 │ 45 Non facilis ex temporibus quia. 1 4 personal 2024-10-06 146
54 │ 58 Libero hic itaque optio. 0 4 work 2024-10-07 36
55 │ 32 Sit molestiae repellat qui nobis. 1 4 family 2024-10-08 222
56 │ 64 Fuga fuga ut. 1 4 work 2024-10-09 42
57 │ 11 Aut officiis qui quaerat. 0 4 shopping 2024-10-15 174
58 │ 21 Et eum saepe voluptas ad. 0 4 hobby 2024-10-15 210
59 │ 67 Occaecati. 0 3 work 2024-10-15 180
60 │ 8 Sed iusto consequatur dolore non… 1 4 other 2024-10-21 106
61 │ 51 Eaque hic similique. 1 3 hobby 2024-10-23 46
62 │ 57 Deserunt minima. 1 3 other 2024-10-26 182
63 │ 19 Aliquid eaque iusto quidem. 0 3 other 2024-10-27 47
64 │ 22 Facilis hic qui sit aliquid faci… 1 4 learning 2024-10-27 159
65 │ 23 Vitae eveniet similique aut dolo… 0 3 personal 2024-10-27 126
66 │ 38 Quaerat. 0 4 accounting 2024-10-31 233
67 │ 10 Ut possimus eum quae est omnis. 1 3 shopping 2024-11-02 208
68 │ 17 Eos eum quos illum voluptatem. 0 3 errands 2024-11-02 199
69 │ 69 Quibusdam velit mollitia. 0 4 learning 2024-11-02 38
70 │ 65 Voluptas beatae qui atque dolore… 0 4 shopping 2024-11-06 82
33 rows omitted
julia> DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE date BETWEEN '2024-10-03' AND '2024-10-27' ORDER BY date " ) |> DataFrame
0×3 DataFrame
Row │ id category date
│ Int64? String? Missing
─────┴───────────────────────────
julia>
```
---
### Author:  [@andreh](https://discourse.julialang.org/u/andreh)
#### Post date: [November 13, 2024, 11:07pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/18 "2024-11-13T23:07:30Z")
but how to do it inside the app , is using SearchLight for example [Todos.jl](https://github.com/PacktPublishing/Web-Development-with-Julia-and-Genie/blob/main/Chapter8/TodoMVC/app/resources/todos/Todos.jl)
and in line 35
> SQLWhereExpression(“date \>= ? AND date \<= ?”, startdate, enddate),
but it returns 0 rows, and I do have values
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [November 14, 2024, 1:04am UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/19 "2024-11-14T01:04:11Z")
is it possible for you to share the data so i can play around with it?
---
### Author:  [@Dan](https://discourse.julialang.org/u/Dan)
#### Post date: [November 14, 2024, 1:48am UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/20 "2024-11-14T01:48:37Z")
> [@andreh](#):
>
> ```julia
> julia> DBInterface.execute(db, "SELECT * FROM todos WHERE date >= '2024-10-03'") |> DataFrame
>
> ```
Try it without the dashes:
```julia
DBInterface.execute(db, "SELECT * FROM todos WHERE date >= '20241003'") |> DataFrame
```
The logic is that in DataFrame form the Dates can be displayed differently to how they are stored in SQLite (which has Text but no Date type). Keep the single quotes. Same for the other query:
```julia
DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE date BETWEEN '20241003' AND '20241027' ORDER BY date " ) |> DataFrame
```
[Next page](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512.md?page=2)