How to format dates in SQLExec query

[TimAndersen]

I am trying to archive Lookout data to an Oracle database using SQLExec. I started with existing SQLExec code that put data into Access. Following is that code:

"Insert into wind_temp (Rdate,Wind,Temp) values ("&text(now(Timer3),"0.00000000")&",
"&text(\\SCADA1\Server\Drivers\Corral\ROCSouth.Wind,"0.000")&",
"&text(\\SCADA1\Server\Drivers\Corral\ROCSouth.Temp,"0.000")&")", 8, no);

The Rdate column in wind_temp table is date type. If I change the type to varchar2 (string) this works, so my ODBC connection is good. The problem is that for date fields Oracle needs a string in DD-MON-YYYY (i.e. 01-JAN-2002) format or string data converted to a date (i.e. to_date('01-JAN-2002 12:00:00','DD-MON-YYYY HH24:MI:SS')). Is there a way
using the now() or other function to build a "to_date" string like above to pass to Oracle?

[tommy_scharmann]

Here you go:

text(now(),"dd/mm/yyyy hh:mm:ss")

This will give you slashes and not dashes and a number for the month and not the abbreviated name.

Here is the expansion that does what you need:

left(text(now(),"dd/mm/yyyy hh:mm:ss"),2)&"-"&if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="01","JAN",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="02","FEB",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="03","MAR",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="04","APR",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="05","MAY",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="06","JUN",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="07","JUL",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="08","AUG",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="09","SEP",if(m
id(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="10","OCT",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="11","NOV",if(mid(text(now(),"dd/mm/yyyy hh:mm:ss"),4,2)="12","DEC","ERROR"))))))))))))&"-"&mid(text(now(),"dd/mm/yyyy hh:mm:ss"),7,15)

The date I got from this was:
11-APR-2002 11:54:35

I would recommend using multiple expression objects to simplify reading the code. I included it here, all in one expression, so you could copy and paste it for testing.

I tested it on Lookout 5.0. It works.

Study the text() function and the general text manipulation functions. You can do just about anything with a little creativity.

Regards,
Tommy Scharmann

[Rkdomer]

The tchoose function could also be useful in this situation to extract the month:

tchoose (numeric,"JAN", "FEB", "MAR", "APR", "MAY", "JUNE", "JULY", "AUG", "SEP", "OCT", "NOV", "DEC", "error")

[tommy_scharmann]

Cool! Even better!

Regards,
Tommy Scharmann