Date variable value in automation email subject in wrong format

Toad Data Point
1

I reviewed the using variables tutorial, and think I have eveyrthing set correctly. When my variable is set, it shows as mm/dd/yyyy but when it comes out on me email it is showing as "To_date(‘07/28/2014 00:00:00.00’, ‘MM/DD/YYYY HH24:MI:SS.SS’).

I am trying to include yesterday’s date in the subject line of the email.

I am using a SQL Server connection; my variable name is YDay; the variable type is SQL; the stmt is: select cast(convert(varchar(8),DATEADD(d,-1,getdate()),1) as date)"; the email subject line is: Comcash Invoices for #YDay#

My results are: Comcash Invoices for To_date(‘07/28/2014 00:00:00.00’, ‘MM/DD/YYYY HH24:MI:SS.SS’)

How do I get rid of this ugly format and get it pretty again?

TIA, Marge

2

You have it right. This looks like a bug. I entered QAT-3599 although I don’t think it will make the TDP 3.6 release.

As a work around change to string type var and use the built in functions like shown below.

ndate.png

3

...just to note I get this issue with SQL Variable result getting date in TDP version 5.3.33 from SQL Server.

4

Just to confirm that you are getting this in automation when you are setting a date variable with a SQL server connection?

5

@Julie_Hyman

Correct. Result of SELECT DISTINCT "REPORT DATE" FROM HP_TX_OWN_TABLES.CURR_PREG_NO_NOP; is:

REPORT DATE: To_date('12/15/2021 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS') IMPORT COUNT: 20599