Search between German dates when they are strings

Question

I have a MVC project with two fields where the user can input two dates.

Model:

[DisplayName("Date From")]
public string DateFrom { get; set; }

[DisplayName("Date To")]
public string DateTo { get; set; }

The two dates are belonging to one column in the database called OriginalDate. This daterange is the result.

In the database the column OriginalDate is a character varying field. The syntax is DD.MM.YYYY.

When the user's input is "Date From" 14.02.2017 (DD.MM.YYYY) and the "Date To" 20.02.2019, how can I reach the result, that my query finds all the results between those two dates?

Considered this statement:

SELECT * FROM tbl.tbl
WHERE OriginalDate BETWEEN '14.02.2017' AND '20.02.2019'

This is giving me a result, but also from other years. For example DD.MM.1998 or DD.MM.2009. How I have to cast that this statement is working?

Then I tried TO_DATE:

SELECT * FROM tbl.tbl
   WHERE OriginalDate BETWEEN TO_DATE('14.02.2017', 'DD.MM.YYYY')
                          AND TO_DATE('20.02.2019', 'DD.MM.YYYY')

There I get this error:

LINE 1: SELECT * FROM tbl.tbl WHERE OriginalDate BETWEEN TO_DAT...
                                                      ^
HINT:  No operator matches the given name and argument type(s).
       You might need to add explicit type casts.
SQL state: 42883

Furthermore is my question, if I cast it to a date, will it find it when the column is a character variable?

How can I solve this problem?

Change the types to `DateTime`, use parameterized queries and pass the actual DateTime values. You're using the wrong type and a string that can't be ordered chronoligically. You'll have to convert the strings to `date` or `datetime` even in the database to be able to perform any kind of range query, or use any date or time related function — Panagiotis Kanavos, Feb 21 '19 at 14:48
The problem with DateTime is, when I use it the input from a user as DD.MM.YYYY is `false` in `ModelState.IsValid`. `ModelState.IsValid` is `true`, when the input is for example `21-10-2017` I can only manage the c# code. I cannot change the varchar in the database, so this is unfortnuately fix. — Spedo De La Rossa, Feb 21 '19 at 14:57
That's not a problem with DateTime, this means you're using *strings* instead of Daters in the model too. If you want optional dates use `DateTime?` as the type. Instead of assuming there's a problem with `DateTime` find out *why* the model is invalid — Panagiotis Kanavos, Feb 21 '19 at 14:59
`when the input is for example 21-10-2017` that's a problem in itself. The client or browser should send ISO8601 dates. Javascript controls can do that easily. The JavaScript `Date` object has a [toISOString](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toISOString) method — Panagiotis Kanavos, Feb 21 '19 at 15:01
Check the [input type='date'](https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/date) input. It's value is in the `YYYY-MM-DD` form — Panagiotis Kanavos, Feb 21 '19 at 15:02

score 0 · Answer 1 · answered Feb 22 '19 at 18:10

You could create this function:

CREATE FUNCTION str2date(text) RETURNS date
   LANGUAGE sql IMMUTABLE AS
$$SELECT to_date($1, 'DD.MM.YYYY')$$;

This is ok, since it is really immutable.

Then you can query:

... WHERE str2date(originaldate) BETWEEN str2date('10.12.2018')
                                     AND str2date('01.01.2019')

You can even create an index to speed that up:

CREATE INDEX ON tbl.tbl (str2date(originaldate));

Of course it would be best to store data as the correct data type. That way you could avoid contortions like that.

Search between German dates when they are strings

1 Answers1