1

I have one field in a Firebird DB containing dates like this

27.09.2014

untill no I compared it to the current date with

substring(100+extract(day from current_date) from 2 for 2)
|| '.' ||
substring(100+extract(month from current_date) from 2 for 2)
|| '.' ||
extract(year from current_date)

= date_field_to_compare

But now i need to keep out the greater dates and this turns out to be impossible in this case, since its not handling native dates but numbers.

So I actually need to do the opposite, to convert the 

27.09.2014

into A SQL date.

Like 

SELECT date_field_to_compare FROM db WHERE
date_field_to_compare < CURRENT_DATE

But how can I Convert this DB Date field into a SQL understandable date?

Michael Müller
  • 371
  • 6
  • 23
  • This is very close, but not entirely a duplicate of your earlier question: http://stackoverflow.com/questions/23846292/select-varchar-as-date – Mark Rotteveel Sep 27 '14 at 07:32
  • And it is also nearly a duplicate of http://stackoverflow.com/questions/23145974/firebird-select-field-from-table-where-field-current-date – Mark Rotteveel Sep 27 '14 at 07:33

1 Answers1

1

You shouldn't store dates in (VAR)CHAR fields, store them in a DATE. It will solve these types of problems, and make things like selecting, sorting etc a lot easier.

That said Firebird supports a number of date conversions from string, and dd.MM.yyyy is one of them:

SELECT CAST('27.09.2014' AS DATE) FROM RDB$DATABASE

Or:

SELECT date_field_to_compare 
FROM db 
WHERE CAST(date_field_to_compare AS DATE) < CURRENT_DATE

Related: https://stackoverflow.com/a/23857635/466862

Community
  • 1
  • 1
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Its an German ERP system where I cant change the DB, so I write into regular VarChar fields. Thanks for your answere that works. But how could I convert empty or non valid data into a generic date like 01.01.2000? – Michael Müller Sep 28 '14 at 12:07
  • @MichaelMüller That isn't easy to do (or at least: the code will be ugly). You might want to consider handling date conversion in your application and not in Firebird. – Mark Rotteveel Oct 02 '14 at 07:25