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

[Richard Harden]

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 |
\**********************************/

[Karl & Betty Schendel]

Post by Richard Harden
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

Dates don't have any format when stored. They are stored as a
binary structure giving the date, and time as GMT if there's a
time part.

Dates are displayed according to the II_DATE_FORMAT setting,
and at present that's almost your only option. You can
also cast the dates to varchar within an SQL query, and
when that's done, the conversion is under the control of
the SET DATE_FORMAT statement, which might be undocumented;
I haven't looked. The server date format is initialized to
the II_DATE_FORMAT when you start a session, but you can
change it with SET DATE_FORMAT. That only affects date
conversions done within the server, though, not outside
conversions which are done when you retrieve a date as a
date type.

Karl

[m***@ctsu.ox.ac.uk]

Hi Karl,

Yep that looks undocumented and the set statement is not accepted
inside a dbms procedure - which is a bummer as it looks kinda useful.

Marty

Post by Karl & Betty Schendel

Post by Richard Harden
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

Dates don't have any format when stored. They are stored as a
binary structure giving the date, and time as GMT if there's a
time part.
Dates are displayed according to the II_DATE_FORMAT setting,
and at present that's almost your only option. You can
also cast the dates to varchar within an SQL query, and
when that's done, the conversion is under the control of
the SET DATE_FORMAT statement, which might be undocumented;
I haven't looked. The server date format is initialized to
the II_DATE_FORMAT when you start a session, but you can
change it with SET DATE_FORMAT. That only affects date
conversions done within the server, though, not outside
conversions which are done when you retrieve a date as a
date type.
Karl
_______________________________________________
Info-Ingres mailing list
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Ball, David]

Richard

If you really must have the date in tableB as a char and formatted as
'dd-mmm-ccyy', then a combination of _date4 and interval should suit.
Here's one I prepared earlier (btw - we have II_DATE_FORMAT = GERMAN):
1) select date('today')

03.08.2007

(1 row)

2) select _date4(interval('seconds',date('today')-date('01-jan-1970')))

03-aug-2007

(1 row)

HTH
Dave

________________________________

From: info-ingres-***@kettleriverconsulting.com
[mailto:info-ingres-***@kettleriverconsulting.com] On Behalf Of
Richard Harden
Sent: 02 August 2007 11:11
To: info-***@kettleriverconsulting.com
Subject: [Info-Ingres] Formatting dates on insert or update - Help
requiredplease



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 |
\**********************************/


_______________________________________________________

Atos Origin and Atos Consulting are trading names used by the Atos Origin group. The following trading entities are registered in England and Wales: Atos Origin IT Services UK Limited (registered number 01245534) and Atos Consulting Limited (registered number 04312380). The registered office for each is at 4 Triton Square, Regents Place, London, NW1 3HG.

This e-mail and the documents attached are confidential and intended solely for the addressee, and may contain confidential or privileged information. If you receive this e-mail in error, you are not authorised to copy, disclose, use or retain it. Please notify the sender immediately and delete this email from your systems. As emails may be intercepted, amended or lost, they are not secure. Atos Origin therefore can accept no liability for any errors or their content. Although Atos Origin endeavours to maintain a virus-free network, we do not warrant that this transmission is virus-free and can accept no liability for any damages resulting from any virus transmitted. The risks are deemed to be accepted by everyone who communicates with Atos Origin by email.
_______________________________________________________

[m***@ctsu.ox.ac.uk]

Hi Guys,

The function _date4() has an output format which is controlled by
II_DATE_FORMAT. So it is not garunteed to get a month in 'mmm'
format.

But the function _date() has a specific output format regardless of the
setting of II_DATE_FORMAT. _date() always outputs in dd-mmm-yy
format.

Note that the year is a two character year not a 4 character year.

Still a little munging with some string functions should be able to fix
that.

Marty

Post by Richard Harden
Richard
If you really must have the date in tableB as a char and formatted as
'dd-mmm-ccyy', then a combination of _date4 and interval should suit.
1) select date('today') 03.08.2007 (1 row) 2) select
_date4(interval('seconds',date('today')-date('01-jan-1970')))
03-aug-2007 (1 row)
HTH
Dave
Formatting dates on insert or update - Help requiredplease
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 |
\**********************************/
_______________________________________________________
Atos Origin and Atos Consulting are trading names used by the Atos
Origin group. The following trading entities are registered in England
and Wales: Atos Origin IT Services UK Limited (registered number
01245534) and Atos Consulting Limited (registered number 04312380).
The registered office for each is at 4 Triton Square, Regents Place,
London, NW1 3HG.
This e-mail and the documents attached are confidential and intended
solely for the addressee, and may contain confidential or privileged
information. If you receive this e-mail in error, you are not
authorised to copy, disclose, use or retain it. Please notify the
sender immediately and delete this email from your systems. As emails
may be intercepted, amended or lost, they are not secure. Atos Origin
therefore can accept no liability for any errors or their content.
Although Atos Origin endeavours to maintain a virus-free network, we
do not warrant that this transmission is virus-free and can accept no
liability for any damages resulting from any virus transmitted. The
risks are deemed to be accepted by everyone who communicates with Atos
Origin by email.
_______________________________________________________

[m***@ctsu.ox.ac.uk]

Hi Paul,

Normally I'd agree with you but I just found out yesterday you can do

But that's my point. Using _date4(), under date_format GERMAN you
get 03.08.2007, but with US the query yields 03-aug-2007. So the
output can't be relied on. Try the query with _date() instead and no
matter what the date format is set to you will get a consistent answer.

Marty

[g***@yahoo.co.uk]

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

[Richard Harden]

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-***@kettleriverconsulting.com
[mailto:info-ingres-***@kettleriverconsulting.com] On Behalf Of
***@yahoo.co.uk
Sent: Tuesday, 7 August 2007 6:39 a.m.
To: info-***@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

[Mike]

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

[--CELKO--]

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.

[s***@gmail.com]

Post by --CELKO--

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.

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?

[--CELKO--]

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.

[s***@gmail.com]

Post by --CELKO--

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.

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.

[Richard Harden]

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 |
\**********************************/

-----Original Message-----
From: info-ingres-***@kettleriverconsulting.com
[mailto:info-ingres-***@kettleriverconsulting.com] On Behalf Of
***@gmail.com
Sent: Friday, 10 August 2007 3:31 p.m.
To: info-***@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Formatting dates on insert or update -
Helprequired please

Post by --CELKO--

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.

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.