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

[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-bounces at kettleriverconsulting.com
[mailto:info-ingres-bounces at kettleriverconsulting.com] On Behalf Of
Richard Harden
Sent: 02 August 2007 11:11
To: info-ingres at 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. 
_______________________________________________________

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.kettleriverconsulting.com/mailman/private/info-ingres/attachments/20070803/18732011/attachment.html