SQL - char field with date - how to search between two dates

Question

I have simple problem with SQL query.. I would like to search fields, where dates begin and ends in specific date and time.

Field is defined as CHAR and have structure: DD.MM.YYYY hh:mm:ss and I cannot change it.

I tried to declare some variables and search by this, also tried by Converting.

This is what I tried and didn't work:

SELECT date FROM table WHERE date BETWEEN '1.01.2017 00:00:00' AND '1.02.2017 23:59:59'

SELECT date FROM table WHERE date >= '1.01.2017 00:00:00' AND date <= '1.02.2017 00:00:00'

SELECT date FROM table WHERE date >= Convert(DATETIME, '1.01.2017', 104) AND date <= Convert(DATETIME, '1.02.2017', 104)

Always after this query I get all dates, not this what I asked.

Your 3rd query is almost correct, you just have to cast the date column to `datetime` datatype. — HoneyBadger, Jul 04 '17 at 06:52
What is `104` in the last query? The arguments to `CONVERT` are `(expression, type)`. — Barmar, Jul 04 '17 at 06:59
@Barmar If you asking about 104... I found this query with meaning: `Convert(datetime, 'date', style)`. — Frihu, Jul 04 '17 at 07:58
@Frihu Are you sure you found it in a page about MySQL? I suspect that's for a different RDBMS. — Barmar, Jul 04 '17 at 08:12
@Frihu That's SQL-Server, not MySQL. When you google, make sure you check what database is being explained. — Barmar, Jul 04 '17 at 08:13
@Barmar ahh okay, nevermind. So for MySQL correct convert structure is `(expression, type)` ? — Frihu, Jul 04 '17 at 08:14
Yes. Do you know how to read MySQL documentation? https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html — Barmar, Jul 04 '17 at 08:16

Barmar · Answer 1 · 2017-07-04T07:01:08.760

0

Your field is not in a format that can be compared lexicographically as strings, so you need to convert it to a date. But it's not in the format that MySQL can parse by default (it expects dates in the format YYYY-MM-DD). So use STR_TO_DATE() to parse it with a format string.

SELECT date
FROM table
WHERE STR_TO_DATE(date, '%d.%m.%Y %H:%i:%s') BETWEEN '2017-01-01 00:00' AND '2017-01-02 23:59:59'

edited Jul 04 '17 at 07:01

answered Jul 04 '17 at 06:55

Barmar

741,623
53
500
612

Yes.Move the piano to the stool – Strawberry Jul 04 '17 at 07:23

score 0 · Accepted Answer · answered Jul 04 '17 at 13:13

I solved my problem. Problem was with that HOW this fields are storage in DB. I thought that they are storage like: DD.MM.YYYY hh:mm:ss, but it was only structure. In DB they are storage like: YYYYMMDDhhmmss and after changes in WHERE query line it works.

SELECT date FROM table WHERE date >= '20170101000000' AND date <= '20170101235959'

score -1 · Answer 3 · edited Apr 04 '21 at 23:18

-1

SELECT date FROM table WHERE STR_TO_DATE(date, '%d.%m.%Y %H:%i:%s') BETWEEN '2017-01-01 00:00:00' AND '2017-02-01 23:59:59'

OR

You can use STR_TO_DATE() function.

SELECT date FROM table WHERE STR_TO_DATE(date, '%d.%m.%Y %H:%i:%s') BETWEEN STR_TO_DATE('1.01.2017 00:00:00','%d.%m.%Y %H:%i:%s') AND STR_TO_DATE('1.02.2017 23:59:59','%d.%m.%Y %H:%i:%s')

You can try above query.

edited Apr 04 '21 at 23:18

halfer

19,824
17
99
186

answered Jul 04 '17 at 06:54

Sagar Gangwal

7,544
3
24
38

`date` is not a `DATETIME`, it's a `CHAR`, so this will perform string comparison, not date comparison. – Barmar Jul 04 '17 at 06:56
The dv is just for saying the same thing twice – Strawberry Jul 04 '17 at 07:22
@Strawberry I had answered 31 min ago and he had 30 min. – Sagar Gangwal Jul 04 '17 at 07:27
As in repeating yourself – Strawberry Jul 04 '17 at 07:30
Where did i repeat? – Sagar Gangwal Jul 04 '17 at 07:34
Thank you, but it doesn't help me.. After this all query which you posted, I get error and nothing show. I can say also that `char` has defined lenght, its 14 digit, maybe is it matters? – Frihu Jul 04 '17 at 07:55
And... Maybe it is also important, I would like to show this char as the same which are defined in table. e.g. Char in table: 1.01.2017 12:41:23 and OUTPUT same as date and structure: 1.01.2017 12:41:23 – Frihu Jul 04 '17 at 08:10

SQL - char field with date - how to search between two dates

3 Answers3