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

[DavidP]

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.

[rtdavis]

Thanks for the reply.  I should have added that I was casting and converting the current Effective Date to a new field with a DATE data type.

Also, is it possible to change the data type in the In-DB SELECT tool?  I don't see that option, as with the normal SELECT tool.  Thanks again.

[DavidP]

Sorry, I made a mistake - the in-db formula tool cast function as a new field of type Date is the right way. In-db Select works differently to normal select - you can't change the data type.

Can you perhaps split it to 2 steps in the formula tool:

1. reduce to a 10 character string

2. Cast the 10 character string as Date

[stapuff]

Wouldn't CAST(LEFT(EFFECTIVE_DATE),10) AS DATE) AS "EFFECTIVE_DATE"

work?

[stapuff]

Actually if it already has the dashes wouldn't left 10 already be read as a date format not requiring it to be cast?

CAST(FISCYEARPER_DATE AS Date) AS "Fiscal Period", (used on 8 char date)

LEFT(FISCYEARPER_DATE,10) AS "Fiscal Period", (used on > 10 char date)

CASE WHEN LENGTH(FISCYEARPER_DATE)>10 THEN LEFT(FISCYEARPER_DATE,10) END AS "Fiscal Period" (used on > 10 char date)

All of these return Date Type for me.

[rtdavis]

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

[stapuff]

Have you tried the Left and Case also?

Have you also tried adding

CAST(EFFECTIVE_DATE AS DATE) AS "Effective_Date"

[stapuff]

I don't use IN-DB tools very often because I find the speed (for what I do) doesn't justify it.

But I just might have to warm up my playground and give this a couple of trys.

Puff

[rtdavis]

I'm starting to think the issue is a bug within the Browse In-DB tool and the field types that the tool displays.  Thanks again for your help.

1. All of the proposed solutions seem to provide the right output in the format of YYYY-MM-DD

2. The In-DB Formula Tool where the field is created correctly sets field type to "Date"

Tool 2 - In-DB Formula Tool

3. A subsequent In-DB Formula Tool correctly shows the field created in the previous formula tool as type "Date" (this is not editable)

Tool 3 - In-DB Formula Tool (just to display data type of field created in Tool 2)

4. However, Metadata in Browse In-DB (output of Original formula tool) still shows field type as string

Tool 4 - Browse In-DB Tool

5. However, after the Data Stream Out, a regular Filter applied to the workflow provides the option to filter the field as a "Date"

Tool 6 - Regular Filter Tool (tool 5 is Data Stream Out)

6. A Summarize Tool placed after the Filter tool correctly shows the data type as "Date"

Tool 7 - Regular Summarize Tool