This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
I think, just like the normal formula tool, you can't change the data type of an existing field (only the multi-field formula can do that), so you have to change the value first with the in-db formula tool and then use an in-db select to change the data type.
Thank you for your replies. All of the suggestions produce what visually looks like the right result, a value in the format of YYYY-MM-DD. However, I still end up with a string field type. See screen shots below.
I have tried writing the SQL convert/cast statements both in the initial SQL in the Connect In-DB tool and also the In-DB Formula tool.
I'll keep playing and see if I can find a resolution. Thank you for taking the time to respond. Randy