Cast String to Date In-Database

[rtdavis]

Hi - I have a new datetime format stored as a string in a SQL Server database that I am trying to convert In-DB, so I can filter on date.

The string format of my "EFFECTIVE_DATE" field is: "2018-12-06T00:00:00.000-08:00"

I have the following cast and convert commands through the In-Database Formula tool.  In both scenarios, the output is the correctly parsed left most piece of the original datetime string value equal to "2018-12-06".  However, the output field type still results in a "V_WSstring", even though my formula tool is set to Type: Date.

• CAST("EFFECTIVE_DATE" as DATE)
• convert(date, "EFFECTIVE_DATE", 126)  (Per an article I read, Style 126 is ISO 8601 format for "yyyy-mm-ddThh:mm:ss"

What is the best way to get the EFFECTIVE_DATE field converted to an actual date using the In-DB tools?

Thanks you - Randy

[myfriend]

Hello, need to covert a string YYYYMMDD to date.  When I use the cast formula it CAST "ERDATE" 20050819 as date it comes out wrong 1970-08-21

[apathetichell]

@myfriendwhat kind of DB are you using? In-DB syntax is vastly different depending upon what kind of back end DB you are using.

[myfriend]

SAP - SnowFlake

[apathetichell]

This is all in-db (ie the SAP data has gone to Snowflake via ETL job or the like)?

to_date('20050819','yyyymmdd')

assuming this is a varchar field - you'd use this in formula in db with:

to_date("datefield",'yyyymmdd') and create a new datefield.

if you need additional help - start a new thread.

[_wlm_horton]

I am using the In-DB tools with SQL Server and am seeing the same results as you. No matter how the date is formatted in the DB, it turns into VW_String as soon as I stream it out to my Alteryx canvas. I assume this is a feature, not a bug.

Thankfully the select tool can transform all of my date fields to date format with just one tool, it doesn't always work that way. Normally I would have to use a Date Time tool for each date.