NVARCHAR date conversion

[former_member344586]

Hi,

I am getting error while converting NVARCHAR date to "Date" , through conversion function Date()

below is the screenshot of definition and error , could you please suggest what I need to do to remove this error.

I have gone through many scn posts but unable to resolve it.

Thanks !

[muthuram_shanmugavel2]

Hi,

Please use the below formula in your calculated column.

format (longdate ("START_DATE"), 'YYYY-MM-DD')

It will convert your NVARCHAR Data Type to DATE Data Type.

Data Preview Result,

Refer this link to get calculated column DATE functions.

Date Functions - Creating a Graphical Calculation View and Previewing its Output - SAP Library

Regards,

Muthuram

[former_member344586]

Hi Muthuram,

I tried this and getting the same error.

Thanks!

[muthuram_shanmugavel2]

Hi,

Please share me "START_DATE" Field Value. I will check it.

[former_member344586]

Hi Muthuram,

Its of the format    20160620

Thanks!

[muthuram_shanmugavel2]

For String, '20160620' - Conversion to Date is working fine for me.

Share me your calculated column screenshot with error details.

Regards,

Muthuram

[former_member344586]

Hi Muthuram,

below is the screenshot of the calculated column and error

Thanks!

[muthuram_shanmugavel2]

Hi Raju,

The problem is you have not included "format" keyword in your expression.

Please use the below formula (which I already shared in last thread) to your editor.

It will work

format (longdate ("START_DATE"), 'YYYY-MM-DD')

[former_member344586]

Hi Muthuram,

getting same error, even after applying the 'format' in the expression.

Thanks!

[former_member344586]

Strange part is that , when I put this Date in Bex query , I can see the "Attribute values" properly as date , date filled correctly.

But when I execute the query it is not working and throws the same error which I am getting when I see the data in HANA view.

Please suggest if any idea.

Many Thanks !

[muthuram_shanmugavel2]

Hi Raju,

I do not understand why you are getting issue again and again?

Using the formula, which I mentioned above, CHAR to DATE conversion could be done easily.

Please Copy - Paste this Formula in your expression and Execute and let me know.

[P.S: I noticed, In your screenshot you used two parentheses after longdate]

format (longdate ("START_DATE"), 'YYYY-MM-DD')

[former_member344586]

Hi Muthuram,

I copied your expression, but same error.

Thanks!

[former_member344586]

Hi Muthuram,

As the conversion issue is not getting resolved , I was just wondering if I can format the NVARCHAR column in the date format. for eg. 20160920 , I want to show as 20.09.2016 , in nvarchar column itself. can we do this kind of formatting for string.

This date is just for display purpose.

Please suggest.

Thanks!

[Former Member]

I am also facing same issue. Did anyone get any solution for this..

Appreciate your response

[lbreddemann]

Hmm.. the error message says:

I cannot use the mask YYYY-MM-DD on the string 20160620 to convert it into a date.

Now, I believe that since 20160620 does not contain any dashes.

Maybe, using the conversion mask YYYYMMDD works here?

[Former Member]

Unfortunately it did not work

Error: [303]: invalid DATE, TIME or TIMESTAMP value:  [303] invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] attribute value is not a date or wrong syntax;daydate comma(daydate daydate(string format(longdate [here]longdate(string "COL$1$"), string 'YYYYMMDD'))),COL$1$ = '?'[string]; checkNull false

Wondering why is it failing, as it seems to be a simple conversion of NVARCHAR to DATE

[lbreddemann]

Ok, this now looks as if some of  the values in this column actually don't conform to the pattern. Are there NULL or empty string values in the column?

[Former Member]

They are empty string values

[Former Member]

Resolved, there was some issue in the Data, converted all the data to NULL and able to resolve it.