SQL Editor Date part

[Hi2023]

Hi, 

New to SQL editor in Alteryx  and I am needing I think a DATE PART function bc I need for REF_Date it always pull back one month when ran

and for the Start_Run Date to be one day before the REF_DAte function. 

Any help would be apprecaited.

thanks

[DataNath]

Hey @Hi2023 are you able to provide a sample of data and show what output you would expect. Also, would help if you could let us know which database you're using?

[Hi2023]

I am using SQL Editor and I currently have it as a manual update each time I run it 

- for ex. 

('4/09/2023') as Ref_Rec_Date

C_ST_STAR_DT >= '2023-04-08'

But, now I want it to be done but not manually

expected for it to be pulled one month back every time run 05/11/2023  and the C_ST_STAR_DT to be one day before 05/10/2023

It is showing as Strings

[SeanAdams]

Hey @Hi2023 ,

Every implementation of SQL is subtly different - so there's no one standard way to solve for date-parts in a SQL DB or in a SQL editor.

For example - to replace a null character in MSSQL you'd use isnull(field,0) but in Oracle or DB2 you may use Coalesce.

My best recommendation is that you use the SQL tool of the dB that you're working with to perfect the query, and then bring this back into Alteryx.    For example - if it's a Snowflake DB then start with the Snowflake Query window until you're getting the right query.

Also - SQL is often not easy to get right - but we how have a great shortcut in the new generation of generative AI - try GPT Chat or Bing Chat and they can often give you the correct syntax straight out of the box.

Have a good week

Sean