OraFAQ Forum: SQL & PL/SQL » Convert a string to a date using TO

Home » SQL & PL/SQL » SQL & PL/SQL » Convert a string to a date using TO_DATE Function in after insert trigger?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Convert a string to a date using TO_DATE Function in after insert trigger? [message #625727]

Mon, 13 October 2014 12:00

mattfriend
Messages: 12
Registered: September 2014

Junior Member

Hi Folks:

I have a table that has a column (called DATE_FED) that must be saved in string format.

After all the data is saved an after insert trigger is fired that copies certain columns to another table.

The problem is that the after insert trigger will not work because the table being copied to stores the DATE_FED data in a Date format.

I am trying to figure out how to use the TO_DATE function to convert the NEW.DATE_FED data to a Date format so the trigger will work.

Here is the trigger as it currently reads:

create or replace TRIGGER "FARM"."BALES_FED_PAST_TRIG" AFTER INSERT ON "FARM"."BALES_FED_PASTURE" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW Declare

BEGIN

insert into BALES_STORAGE (BALES_LOST_SUBTRACTED, CROP, CUTTING, STACKYARD, DATE_MOVED, FED_ID, BALE_YEAR) values (:new.BALES_FED, :new.CROP, :new.CUTTING, :new.STACKYARD, :new.DATE_FED, :new.FED_ID, :new.BALE_YEAR);

END;

This trigger worked before I had to change to column type of DATE_FED from Date to String.

I have tried things like:
TO_DATE(DATE_FED) := NEW.DATE_FED; in a declare statement but I'm not getting it.

Summary:
I need an after insert trigger to convert NEW.DATE_FED (a string) to NEW.DATE_FED (a Date).

Tables:
BALES_FED_PASTURE is the table the trigger is running on.
BALES_STORAGE is the table the trigger is copying certain columns to.

Thank you very much for any help.

Matthew

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625728 is a reply to message #625727]

Mon, 13 October 2014 12:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/database/121/SQLRF/functions218.htm#SQLRF06132

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625731 is a reply to message #625727]

Mon, 13 October 2014 12:20

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Are you certain that a trigger is the best way to do this? I always take the approach that triggers are a Bad Thing, to be avoided if at all possible. Could you use a multitable INSERT statement instead? Examples,
http://docs.oracle.com/database/121/SQLRF/statements_9014.htm#i2125362

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625733 is a reply to message #625727]

Mon, 13 October 2014 12:39

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

mattfriend wrote on Mon, 13 October 2014 13:00

I have tried things like:
TO_DATE(DATE_FED) := NEW.DATE_FED; in a declare statement but I'm not getting it.

Summary:
I need an after insert trigger to convert NEW.DATE_FED (a string) to NEW.DATE_FED (a Date).

TO_DATE requires a format string. You have none.
How do you distinguish "NEW.DATE_FED (a string) to NEW.DATE_FED (a Date)?"
You would use TO_DATE on the string, not the DATE.
NEW values are referred to with a colon.

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625744 is a reply to message #625733]

Mon, 13 October 2014 14:00

mattfriend
Messages: 12
Registered: September 2014

Junior Member

Hi Joy:

Thank you for trying to help, I appreciate it.

I tried a few variations of the following with what you said:

create or replace TRIGGER "FARM"."BALES_FED_PAST_TRIG" AFTER INSERT ON "FARM"."BALES_FED_PASTURE" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW Declare

:NEW.DATE_FED := TO_DATE(:NEW.DATE_FED, 'MM/DD/YYYY');

BEGIN
  
 insert into BALES_STORAGE (BALES_LOST_SUBTRACTED, CROP, CUTTING, STACKYARD, DATE_MOVED, FED_ID, BALE_YEAR) values (:new.BALES_FED, :new.CROP, :new.CUTTING, :new.STACKYARD, :new.DATE_FED, :new.FED_ID, :new.BALE_YEAR);

END;

The trigger still won't compile.

I'm probably doing something dumb....

Matthew

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625745 is a reply to message #625731]

Mon, 13 October 2014 14:06

mattfriend
Messages: 12
Registered: September 2014

Junior Member

Hi John:

I'm using a php based program (PHPMaker 11) for generating the data entry screen. I don't understand their code well enough to change the sql for inserting.
I'm guessing though that even if I did I would not be able to insert the data into the 2nd table because of the mismatch between the data in string format and the 2nd table needing Date format.

I appreciate your help.

Matthew

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625746 is a reply to message #625745]

Mon, 13 October 2014 14:33

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is the actual type of DATE_FED?
If it is a date then "TO_DATE(:NEW.DATE_FED, 'MM/DD/YYYY')" is wrong.
If it is a string then ":NEW.DATE_FED := TO_DATE..." is wrong.

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625748 is a reply to message #625746]

Mon, 13 October 2014 15:13

Bill B
Messages: 1971
Registered: December 2004

Senior Member

you can convert in the insert command. The following code assumes that the layout of :new.date_fed is mm/dd/yyyy. If it isn't you have to change the format mask.

create or replace TRIGGER "FARM"."BALES_FED_PAST_TRIG" 
AFTER INSERT ON "FARM"."BALES_FED_PASTURE" 
REFERENCING OLD AS "OLD" NEW AS "NEW" 
FOR EACH ROW 
BEGIN

insert into BALES_STORAGE (BALES_LOST_SUBTRACTED, CROP, CUTTING, STACKYARD, DATE_MOVED, FED_ID, BALE_YEAR) values (:new.BALES_FED, :new.CROP, :new.CUTTING, :new.STACKYARD, TO_DATE(:new.DATE_FED,'mm/dd/yyyy'), :new.FED_ID, :new.BALE_YEAR);

END;

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625749 is a reply to message #625746]

Mon, 13 October 2014 15:15

mattfriend
Messages: 12
Registered: September 2014

Junior Member

Hi Michel:

The BALES_FED_PASTURE (the table the data is going to be copied from with the trigger) is:
"DATE_FED" VARCHAR2(18), (so it is saved in string format as MM/DD/YYYY)

The BALES_STORAGE table is the table the data is being copied too.
"DATE_MOVED" DATE, is the column I need to copy the DATE_FED data too.

The actual Table sql is:

 CREATE TABLE "BALES_STORAGE" 
   (	"BALES_LOST_SUBTRACTED" NUMBER(5,0) DEFAULT NULL, 
	"CROP" VARCHAR2(20 CHAR), 
	"CUTTING" NUMBER(5,0), 
	"STACKYARD" VARCHAR2(25), 
	"DATE_MOVED" DATE, 
	"STORAGE_ID" NUMBER(15,0), 
	"BALES_RECEIVED_GAINED" NUMBER(5,0), 
	"FED_ID" NUMBER(15,0), 
	"HARVEST_ID" NUMBER(15,0), 
	"CONSUMED_ID" NUMBER(15,0), 
	"MOVED_ID" NUMBER(15,0), 
	"STOCKTAKE_ID" NUMBER(15,0), 
	"BALE_YEAR" VARCHAR2(4), 
	"FED_DEST_ID" NUMBER(15,0)
   ) ;
--------------------------------------------------------
--  DDL for Table BALES_FED_PASTURE
--------------------------------------------------------

  CREATE TABLE "BALES_FED_PASTURE" 
   (	"BALES_FED" NUMBER(5,0) DEFAULT NULL, 
	"CROP" VARCHAR2(20 CHAR), 
	"CUTTING" NUMBER(5,0), 
	"STACKYARD" VARCHAR2(25), 
	"DATE_FED" VARCHAR2(18), 
	"PASTURE" VARCHAR2(25), 
	"TARGET_LB_PER_DAY" NUMBER(3,1), 
	"FED_ID" NUMBER(15,0), 
	"SPECIES" VARCHAR2(30), 
	"BALE_YEAR" VARCHAR2(4), 
	"POLE_SHED" VARCHAR2(8), 
	"USERNAME" VARCHAR2(40), 
	"STORAGE_SUMMARY" VARCHAR2(80)
   ) ;

[Updated on: Mon, 13 October 2014 15:16]

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625750 is a reply to message #625749]

Mon, 13 October 2014 15:26

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Bill's answer is what you want. See how much cleaner it looks?

I must ask though, WHY does the date have to be stored as a character string? Is it a lazy programmer or a clueless manager who said so?

Additionally, I don't see any purpose of having the second table as a subset of the first table. That's double the storage for one set of data. What happens when someone updates the second table and now they are out of synch?

[Updated on: Mon, 13 October 2014 15:31]

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625751 is a reply to message #625750]

Mon, 13 October 2014 15:36

mattfriend
Messages: 12
Registered: September 2014

Junior Member

Thank you so much Bill... it worked perfectly. Thank you also to Joy, Michel and others for all the help... I appreciate it very much.

To answer your question about why it had to be stored as a character string Joy:
I am using a program called "PHPMaker 11" to generate the data entry pages. There are about 1500 different possible pastures where the animals can feed.
This is way too much for a drop down list so I am using several different "parent" fields to filter the results that the end user can pick from in the pasture drop down field.
So First the pasture field is filtered by species (i.e. cattle), by the DATE_FED, and Pole Shed. The animals are in a particular pasture (DATE_IN) and eventually leave or are moved (DATE_OUT), or if currently in the pasture null.

Anyways some of the stakeholders wanted to use the DATE FED as a check to help limit and make sure the pasture is correct. PHPMAKER though does NOT allow a date field to be used as a parent filter. To be able to filter using the DATE FED column I had to change it in the lookup table to a string instead of date. So I guess to answer your question... it had to be converted as a result of a weakness in the program.

Again... thank you very, very much everyone!!

Matthew

Report message to a moderator

Re: Convert a string to a date using TO_DATE Function in after insert trigger? [message #625780 is a reply to message #625751]

Tue, 14 October 2014 03:43

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If the program can't handle dates then it would probably be better to create a view over the table that converts the date in the table to a string. Then have the phpmaker program query the view instead of the table.

Report message to a moderator

Previous Topic:	The optimal approach to this search
Next Topic:	PLS-00497:cannot mix between single row and multi-row (BULK) in INTO LIST

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 19:41:02 CDT 2024