I have SQL 2005 table with DateTime field Data Type. Table name is TALERTS_SENT. Field name is DATE_SENT. This field is DateTime Data Type. I need to be able to insert records to this table from WebFocus procedure (*.fex).
If I do this
SQL
INSERT INTO TALERTS_SENT (ALERT_NAME, JOB_ID, DATE_SENT, SENT_TO) VALUES
('AN2', 1, 9/26/2007, 'test2@aol.com');
END
or this
SQL
INSERT INTO TALERTS_SENT (ALERT_NAME, JOB_ID, DATE_SENT, SENT_TO) VALUES
('AN2', 1, &DATEMDYY, 'test2@aol.com');
END
then I get error
(FOC14069) SYNTAX ERROR ON LINE 2 AT '/' -- Expected ')'
So it doesn't like forward slash inside date value. How can I insert date value in DateTime field?
Note -
I was able to prove that this problem is related to DateTime field.
I created new table tAlerts_Test1 in SQL 2005 with the same field names, but I did change Data Type for Date_Sent field from datetime to
nvarchar(30).
I was able to run the code below from WebFocus and it successfully inserted row in tAlerts_Test1 table.
SQL
INSERT INTO DW.TALERTS_TEST1 (Alert_Name, Job_ID, Date_Sent, Sent_To)
VALUES
('AN4', 4, '09/26/2007', 'test4@aol.com');
END

Hi Seva,

I think the datetime is expecting something like "2005/1/1 00:00:00" . Can you not change the type to varchar?

Yes, I just figured it out that datetime will accept something like '2007/09/26 12.12.12', so now I know what I should do. Thanks.

It works if I do just 1 line insert using 'Values' syntax like this:

 -SET &TIME = EDIT(&TOD,'99:$99:$99')||'.000';
-SET &DAY = EDIT(&YYMD,'9999/99/999');
-SET &TS = &DAY||&TIME;
-TYPE "TS is " &TS
SQL
  INSERT INTO DW.TALERTS_SENT (ALERT_NAME, JOB_NUMBER, DATE_SENT, SENT_TO)
  VALUES ('Buyout-PM','987','&TS','test987@aol.com');
END 

If I try to insert multiple lines like this:

 ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV
SQL SQLMSS PREPARE SQLOUT FOR
  SELECT 'Buyout-PM' AS ALRTNM, RTRIM(F2.[JOB NUMBER]) AS JOBNUM, GETDATE() AS CURDATE, F1.[PROJECT MANAGER EMAIL] AS PMEMAIL
  FROM ...
  WHERE ...
END
TABLE FILE SQLOUT
  PRINT *
  ON TABLE HOLD AS #HLIST1 FORMAT SQLMSS
END
-RUN
-*-- Insert all the rows from the temporary table ----------
SQL
  INSERT INTO DW.TALERTS_SENT (ALERT_NAME, JOB_NUMBER, DATE_SENT, SENT_TO)
  SELECT ALRTNM, JOBNUM, CURDATE, PMEMAIL FROM #HLIST1
  ;
END
-RUN 

then it fails with errors like

 (FOC428)TRANS     1 REJECTED  FORMAT  COL    43 FLD DATE_SENTBuyout-PM6           20071008115040807test123@aol.com 

Any ideas?

btw, i don't think you have to have the timestamp to insert. SQL expects datetimes to be in quotes (eg, '9/27/2007')

Mark,
looks like I do have to have timestamp because if I do

 -SET &TIME = EDIT(&TOD,'99:$99:$99')||'.000';
-SET &DAY = EDIT(&YYMD,'9999/99/999');
-SET &TS = &DAY||&TIME;
-TYPE "TS is " &TS
-*-- Insert all the rows from the temporary table ----------
SQL
  INSERT INTO DW.TALERTS_SENT (ALERT_NAME, JOB_NUMBER, DATE_SENT, SENT_TO)
  SELECT ALRTNM, JOBNUM, '&DAY', PMEMAIL FROM #HLIST1
  ;
END
-RUN 

then error says

 (FOC14091) INSERT VALUE FOR 'DATE_SENT' IS NOT COMPATIBLE WITH COLUMN FORMAT 

However, if I do

 -*-- Insert all the rows from the temporary table ----------
SQL
  INSERT INTO DW.TALERTS_SENT (ALERT_NAME, JOB_NUMBER, DATE_SENT, SENT_TO)
  SELECT ALRTNM, JOBNUM, '&TS', PMEMAIL FROM #HLIST1
  ;
END
-RUN 

then error says

 (FOC428)TRANS     1 REJECTED  FORMAT  COL    43 FLD DATE_SENT 

. As you see, I included this field in single quotes. So, based on the 1st example I do need timestamp and format should be like '2007/10/08 13:07:48.000'. Any ideas how to fix error in the 2nd example?
Thanks

Seva,

Try it in one process rather than hold it temporarily first -

ENGINE SQLMSS SET DEFAULT_CONNECTION DW_DEV_SV
SQL SQLMSS
  INSERT INTO DW.TALERTS_SENT (ALERT_NAME, JOB_NUMBER, DATE_SENT, SENT_TO)
  SELECT 'Buyout-PM' AS ALRTNM, RTRIM(F2.[JOB NUMBER]) AS JOBNUM,
          GETDATE() AS CURDATE, F1.[PROJECT MANAGER EMAIL] AS PMEMAIL
  FROM ...
  WHERE ...
END
TABLE FILE SQLOUT
  PRINT *
END
-RUN

Unless there's a reason that you're holding it?

T

Tony,
your suggestion worked. THANK YOU VERY MUCH!
Seva

You are most welcome, glad it helped

T