[Info-ingres] The 'is ingresdate' predicate and II_DATE_FORMAT

[Martin Bowes]

Hi All,

 

In my innocence I assumed that the ‘is ingresdate’ predicate would work using the current session setting of II_DATE_FORMAT. That variable can after all be set locally to override the global setting used in the installation. Given that many input date formats are valid in some settings but not in others, using the session setting makes sense to me.

 

So for example my installation has II_DATE_FORMAT=multinational4. So todays date would be generated as ‘06/04/2021’

 

But…

EXPORT II_DATE_FORMAT=SWEDEN

sql iidbdb <<SQL_END

select dbmsinfo(‘date_format’);

┌────────────────────────────────────────────────────────────────┐

│col1                                                            │

├────────────────────────────────────────────────────────────────┤

│finland                                                         │

└────────────────────────────────────────────────────────────────┘

select date('today')\g

┌─────────────────────────┐

│col1                     │

├─────────────────────────┤

│2021-04-06               │

└─────────────────────────┘

(1 row)

select case when '2021-04-06' is ingresdate then 'valid' else 'invalid' end\g

┌───────┐

│col1   │

├───────┤

│invalid│ ß Well that’s a bit of a shock.

└───────┘

(1 row)

select case when '06/04/2021' is ingresdate then 'valid' else 'invalid' end

┌───────┐

│col1   │

├───────┤

│valid  │ß This is correct, as that date string is valid date input in Sweden

└───────┘

(1 row)

 

So is the ‘is ingresdate’ predicate only respecting the installation wide setting of II_DATE_FORMAT or is this a bug in the processing?

 

Note I’ve tried the above using the set date_format ‘sweden’ command with the same result.

 

Martin Bowes

[Martin Bowes]

Hi Karl,

If they have fins, do they even have toes?

This is sounding more like an issue I'll raise to seek clarification.

Marty

-----Original Message-----
From: Karl Schendel <sche...@kbcomputer.com>
Sent: 06 April 2021 10:37
To: Martin Bowes <martin...@ndph.ox.ac.uk>
Subject: Re: [Info-ingres] The 'is ingresdate' predicate and II_DATE_FORMAT

Marty, you're looking for consistency. Ok,

But wait. You asked for Sweden date format and it told you Finland.
That should have been your first hint...those Finns can't be trusted to count their toes...

(and if you pretend that anyone from Actian answered you, see trump, matt gaetz, etc etc!)

Karl

> _______________________________________________
> Info-ingres mailing list
> Info-...@lists.planetingres.org
> https://lists.planetingres.org/mailman/listinfo/info-ingres

[Steve]

Hi Martin

I can't answer your question, but where I currently work it always take me a couple of goes to get the date literals right when running adhoc queries (which I put down to user error). I don't recall having this problem in the past and we're not using anything as exotic as Swedish or Finnish date formats.

Steve