Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Cast String to Date In-Database

rtdavis
7 - Meteor

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

 

 

13 REPLIES 13
myfriend
5 - Atom

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
18 - Pollux

@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
5 - Atom

SAP - SnowFlake

apathetichell
18 - Pollux

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.

Labels