0

I have a textfield where two dates are stored, separated by "-" .

10.11.2022-15.11.2022

I select the first part as date

SELECT
  ART.ID,
  CAST(RIGHT(ART.FIELD1, POSITION('-' IN ART.FIELD1) - 1) AS DATE)
FROM
  PRODUCTS ART

Problem is if the textfield does not contain the right format, I get an error.

 1h32i2o-15.11.2022

So, I need a function in the WHERE clause which validates if the first part contains data that can be cast as DATE.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Michael Müller
  • 371
  • 6
  • 23
  • 1
    You should validate this *before* it is stored in the database, in fact, you should have two separate fields for this in your database. In any case, this is far easier solved in Firebird 3.0 and higher than in Firebird 2.5. In Firebird 2.5 you'll need to write (or find) a native UDF that does this for you, in Firebird 3.0 you would be able to write a PSQL function. Alternatively, solve this in your presentation layer instead of trying to do this in the query. – Mark Rotteveel Nov 13 '22 at 11:13
  • Does this answer your question? [Firebird SQL test if string contains DATE in WHERE clause](https://stackoverflow.com/questions/50976473/firebird-sql-test-if-string-contains-date-in-where-clause) – Mark Rotteveel Nov 13 '22 at 11:18
  • Also: [Firebird 2.5x CAST Date from String](https://stackoverflow.com/questions/50661153/firebird-2-5x-cast-date-from-string) – Mark Rotteveel Nov 13 '22 at 11:24
  • Another duplicate: [Cast as Int only when character is a number on Firebird 2.5](https://stackoverflow.com/questions/7288228/cast-as-int-only-when-character-is-a-number-on-firebird-2-5) – Mark Rotteveel Nov 13 '22 at 11:34
  • Violation of 1NF always fire back... – user13964273 Nov 13 '22 at 12:04
  • @Mark Rotteweel - This is not my DB I cant change anything, I just can read out. – Michael Müller Nov 13 '22 at 23:08
  • The first duplicate I linked is basically the same question you asked 4 years ago, you'll just need to modify the pattern a bit. – Mark Rotteveel Nov 14 '22 at 08:56
  • Thank you. I thought there would be a special function like in MS SQL "IsDate()" or alike. – Michael Müller Nov 15 '22 at 00:45

0 Answers0