Toad World® Forums

Expression Builder Issue


#1

Building on my previous attempt (successful) to automated a daily report for the current day, I’m now trying to build one that runs for the previous day.

The select is fine and works perfectly. The automation script is great as well. The problem I’m having is generating the previous day’s date in the format that I need.

I’m using: DateAdd(“d”,-1,Now()) which gives me the correct date, but in a format that I can’t use in the file name (ie. 12/15/2010 7:52:27 PM). What I really need is to return a date in the format of YYYYMMDD or 20101215 as the date is inserted as part of the export file name.

I’ve tried variations on Format(Date(‘yyyyMMdd’),“yyyymmdd”) which I had used in my same-day automation script, but haven’t had any success (Expression Builder indicates an error).

Anyone have any ideas?


#2

Why not bounce a SQL statement off of a database to get the date you want and set the variable value? I find that more flexible than using the VB functions. Here is an example in Oracle. You need to surround the date string in quotes. This is because the variable code trips up on the hyphens.

select ‘"’||to_char(sysdate-7,‘MM-DD-YYYY HH24:MI:SS’)||’"’ dt from dual;

Debbie


#3

Probably because I didn’t realize I could do that? I took your example and modified it so that it becomes select ‘"’||to_char(sysdate-1,‘YYYYMMDD’)||’"’ dt from dual to give me the result I need. My only question is, the output shows the value in “” marks - will TDA automatically drop those when assigning the value to the variable? I’m asking because this variable fills in part of the filename, and I can’t use " in a filename.

Additionally, I can see many potential uses for using sysdate to help me get the information I need - thanks!

Debbie Peabody wrote:

Why not bounce a SQL statement off of a database to get the date you want and set the variable value? I find that more flexible than using the VB functions. Here is an example in Oracle. You need to surround the date string in quotes. This is because the variable code trips up on the hyphens.

select ‘"’||to_char(sysdate-7,‘MM-DD-YYYY HH24:MI:SS’)||’"’ dt from dual;

Debbie


#4

Yes. The quotes are removed when the variable code uses the string.

Glad to give you new tools to use. I like bouncing dates off of the database as it is very versatile.

Have fun!

Debbie