[Info-Ingres] Formatting dates on insert or update

[Info-Ingres] Formatting dates on insert or update - Help required please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard Harden - 02 Aug 2007 11:10 GMT

Hi all,

I have a small database procedure that on insert into tableA, it inserts a
related row into tableB
Where currently it uses

<snipped for brevity of course>
Insert into std_activity_references (:std_activity_no,'WP Rev
Date',char(date('today'),10) )
<snip...>

Where because ii_date_format is set to Multinational4, the dates thus
inserted are in the form dd/mm/yyyy.

Is there any simple way of formating the dates so that it is inserted into
the record in the format dd-mmm-yyyy

Someone suggested using set date_format='US' in the procedure, but did say
that this method is fraught..

I tried it in an isql session, but it did not seem to make any difference.

II_DATE_FORMAT =MULTINATIONAL4 is set in each PC's system environment
variables, and in the servers, and using INGSETENV

Installation is II2.6sp3 on Solaris 7 on sparc.

TIA. ;)

Cheers

Richard

/**********************************\

| New Zealander, leading the world |

\**********************************/

Reply to this Message

ghingres@yahoo.co.uk - 06 Aug 2007 19:38 GMT

Hi Richard,

Hope you've got your Jandals on for this one... :-)

Why not use the simple method of SQL, here are two examples for DD-
MMM-YYYY and YYYYMMDD etc...

SELECT
RIGHT('00'+VARCHAR(date_part('DAY',hht_start_date)),2)+'-'+
RIGHT(LEFT('JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC,',
(INT2(date_part('MONTH',hht_start_date)) * 4) - 1
),3)+'-'+
RIGHT('0000'+VARCHAR(date_part('YEAR',hht_start_date)),4) AS
start_date1,

RIGHT('0000'+VARCHAR(date_part('YEAR',hht_start_date)),4)+
RIGHT('00'+VARCHAR(date_part('MONTH',hht_start_date)),2)+
RIGHT('00'+VARCHAR(date_part('DAY',hht_start_date)),2) AS
start_date2,

Just swap hht_start_date for 'today' or your local variable etc...

Enjoy

Gary

Reply to this Message

Richard Harden - 07 Aug 2007 05:56 GMT

Hi All,

A big thank you to all who provided suggestions and/or solutions both
on-line and off-line

Thanks esp. Gary and Dave

Dave's solutions (off-line) was perhaps the more elegant and simpler.

select right(varchar(100+date_part('day','today')),2) +
case
when date_part('month','today')= 1 then '-jan-'
when date_part('month','today')= 2 then '-feb-'
when date_part('month','today')= 3 then '-mar-'
when date_part('month','today')= 4 then '-apr-'
when date_part('month','today')= 5 then '-may-'
when date_part('month','today')= 6 then '-jun-'
when date_part('month','today')= 7 then '-jul-'
when date_part('month','today')= 8 then '-aug-'
when date_part('month','today')= 9 then '-sep-'
when date_part('month','today')= 10 then '-oct-'
when date_part('month','today')= 11 then '-nov-'
when date_part('month','today')= 12 then '-dec-'
end +
varchar(date_part('year','today'));

(Well to me anyway as at least I sort of understood it at first look)

But Gary's I'm going to have to work through carefully to make sure I
understand how/why it works

It might be that there is less overhead in one or the other, but without
testing I won't open mouth and put both feet in ;(

In any case, I appreciate all help provided

Thanks again.

Cheers

Richard

/**********************************\

| New Zealander, leading the world |

\**********************************/

-----Original Message-----
From: info-ingres-bounces@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of
ghingres@yahoo.co.uk
Sent: Tuesday, 7 August 2007 6:39 a.m.
To: info-ingres@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Formatting dates on insert or update -
Helprequired please

Hi Richard,

Hope you've got your Jandals on for this one... :-)

Why not use the simple method of SQL, here are two examples for DD-
MMM-YYYY and YYYYMMDD etc...

SELECT
RIGHT('00'+VARCHAR(date_part('DAY',hht_start_date)),2)+'-'+
RIGHT(LEFT('JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC,',
(INT2(date_part('MONTH',hht_start_date)) * 4) - 1
),3)+'-'+
RIGHT('0000'+VARCHAR(date_part('YEAR',hht_start_date)),4) AS
start_date1,

RIGHT('0000'+VARCHAR(date_part('YEAR',hht_start_date)),4)+
RIGHT('00'+VARCHAR(date_part('MONTH',hht_start_date)),2)+
RIGHT('00'+VARCHAR(date_part('DAY',hht_start_date)),2) AS start_date2,

Just swap hht_start_date for 'today' or your local variable etc...

Enjoy

Gary

_______________________________________________
Info-Ingres mailing list
Info-Ingres@kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

Reply to this Message

Mike - 07 Aug 2007 10:02 GMT

The YYYY_MM_DD format works under all II_DATE_FORMAT's
so here is my solution:

select c(date_part('year', 'today'))+'_'+c(date_part('month', 'today'))
+'_'+c(date_part('day', 'today'))

or if you want leading zeros:

select right(varchar(date_part('year', 'today')+10000),4) + '_'
+ right(varchar(date_part('month', 'today')+100),2) + '_'
+ right(varchar(date_part('day', 'today')+100),2)

Mike

Reply to this Message

--CELKO-- - 08 Aug 2007 14:51 GMT

>> Where because ii_date_format is set to Multinational4, the dates thus inserted are in the form dd/mm/yyyy. Is there any simple way of formating the dates so that it is inserted into

the record [sic] in the format dd-mmm-yyyy <<

This is one of the many differences between rows and records. A
column in a row has a data type and domain, while fields do not.
Temporal data has an internal representation that has nothing to do
with the display. In fact the **only** format allowed in Standard
ANSI/ISO SQL is "yyyy-mm-dd" and that is all you should be using.

Using those local format is a total violation of a tiered architecture
and good SQL programmers avoid them. Let the application programmers
worry about display; we are in charge of data and we follow
standards!

That said, another trick for validation of input strings is to set up
a table with your various formats and do a look-up to convert them.
Usually a 100 years is enough. Uses an OUTER JOIN to find invalid
dates in either format.

CREATE TABLE FixBadDates
(sql_date CHAR (10) NOT NULL,
bad_date CHAR (10) NOT NULL,
etc)

INSERT INTO FixBadDates VALUES ('2007-01-01', '01-jan-07', ..);

The JOIN to the FixBadDates table can be faster than using row at a
time string manipulations that cannot get to an index or hash. And
this is portable, too.

Reply to this Message

s.anderson.au@gmail.com - 09 Aug 2007 15:24 GMT

> >> Where because ii_date_format is set to Multinational4, the dates thus inserted are in the form dd/mm/yyyy. Is there any simple way of formating the dates so that it is inserted into
>
[quoted text clipped - 26 lines]
> time string manipulations that cannot get to an index or hash. And
> this is portable, too.

That's an interesting approach CELKO using the FixBadDates table,
though I am puzzled why you need to worry about how date information
is stored in a table. Isn't it more appropriate for the application
to worry about/manage the displaying of dates in the 'correct'
format? With Ingres doing such a wonderful job of storing dates, via
the date datatype, I don't understand why one would choose to store
date values as character strings?

Of course I could be completely missing the point!

Go the Kiwis! Was does Orcon use Ingres for, just curious?

Reply to this Message

--CELKO-- - 09 Aug 2007 19:44 GMT

>> Of course I could be completely missing the point! <<

I don't want to store it that way; I want to use that look-up table
for data scrubbing and input validation. It beat the heck out of
complex string manipulation in procedural code.

Reply to this Message

s.anderson.au@gmail.com - 10 Aug 2007 04:30 GMT

> >> Of course I could be completely missing the point! <<
>
> I don't want to store it that way; I want to use that look-up table
> for data scrubbing and input validation. It beat the heck out of
> complex string manipulation in procedural code.

Reply to this Message

Richard Harden - 10 Aug 2007 07:23 GMT

Sorry Guys,

Clarification as requested.

I have an application that manages maintenance for equipment
The actual maintenance activity is stored as a MS word document in a public
location

The meta data for that document is in an ingres database
in this case std_activity, PK=std_activity_no (varchar say 10)

Other information is stored in an additional table std_activity_references (
Std_activity_no varchar(10),
Ref_prompt varchar(50)
Ref_data varchar(80))

Whose key is std_activity_no, ref_prompt

One set of the rows stored for a given std_activity_no
Is "A00000001","Document Revision Number","10"
"A00000001","Document Last Modified","10-May-2007"
"A00000001","Book of Reference","BRM2002 (01) (01)"

Or any other additional information relevant to that Maintenance operation.

The product is a COTS product, so we cannot modify the underlying table
schema's
So we make use of what we can.

In this case by storing the Document last modified date in the ref_data for
a given maintenance operation
We use this for off-line processing (read MS access reporting where we are
comparing 'Document last modified' dates for the same std_activity across
two different ingres databases using ODBC linked tables) to determine if a
newly updated (or created) document has to be distributed to
the necessary recipients.

There may well be better ways to do this, but for us, using date stored in a
specific format enables us to do pan-database queries and comparisons
within the capability of our limited access programming skills without
having to try to get the developers to modify the products schema.

Cheers

Richard

/**********************************\

| New Zealander, leading the world |

No, I understand what you were trying to do with your FixBadDate table, but
I don't understand why Richard would need to be concerned about how dates
are stored by Ingres. I like you approach with the FixBadDate table, it's
pretty cool.

_______________________________________________
Info-Ingres mailing list
Info-Ingres@kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

Reply to this Message