SQL Date Formatting Issue

[RCern]

I am connecting to a certain DB and using the "Connect IN DB" tool.  In the SQL Editor I have this:

Select
T0."GRP_NBR_PFX" || LPAD(T0."GRP_NBR_SFX",6,'0'),
T0."GRP_NAM_1",
T0."GRP_ORL_CTR_DAT",
T0."GRP_STS_CD",
T0."GRP_TPA_CTA_CD"
From "GO"."GRP_HST" T0
Where (T0."GRP_APT_CTA_CD" = 'Yxx246'
And T0."GRP_ORL_CTR_DAT" > {d '2019-01-01'}
)

I get the Error: ORA-00936: missing expression error.  I'm not sure how to adjust the SQL for the date so that Alteryx accepts it.  This is how the SQL is written in another DB and it works perfectly fine, but I have a feeling Alteryx requires it differently, but I am not sure how to format that date for it to accept it.  Any ideas?

[DataNath]

Hey @RCern, if you try this without the d{}, does that work?

Select
T0."GRP_NBR_PFX" || LPAD(T0."GRP_NBR_SFX",6,'0'),
T0."GRP_NAM_1",
T0."GRP_ORL_CTR_DAT",
T0."GRP_STS_CD",
T0."GRP_TPA_CTA_CD"
From "GO"."GRP_HST" T0
Where (T0."GRP_APT_CTA_CD" = 'Yxx246'
And T0."GRP_ORL_CTR_DAT" > '2019-01-01')

If not, can you provide a sample of your data please, or some screenshots?

[RCern]

When I do that I get this error:  Error: ORA-01861: literal does not match format string

[RCern]

Here is the data when I run it in my other DB with the original SQL

[DataNath · Accepted solution]

@RCern can you try using TO_DATE?

Select
T0."GRP_NBR_PFX" || LPAD(T0."GRP_NBR_SFX",6,'0'),
T0."GRP_NAM_1",
T0."GRP_ORL_CTR_DAT",
T0."GRP_STS_CD",
T0."GRP_TPA_CTA_CD"
From "GO"."GRP_HST" T0
Where (T0."GRP_APT_CTA_CD" = 'Yxx246'
And T0."GRP_ORL_CTR_DAT" > To_date('2019-01-01','YYYY-MM-DD'))

I've used YYYY-MM-DD above so just switch it it YYYY-DD-MM if necessary.

[RCern]

Thank you!