cancel
Showing results for 
Search instead for 
Did you mean: 

NVARCHAR date conversion

former_member344586
Participant
0 Kudos
2,698

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 !

View Entire Topic
muthuram_shanmugavel2
Contributor
0 Kudos

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
Participant
0 Kudos

Hi Muthuram,

I tried this and getting the same error.

Thanks!

muthuram_shanmugavel2
Contributor
0 Kudos

Hi,

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

former_member344586
Participant
0 Kudos

Hi Muthuram,

Its of the format    20160620

Thanks!

muthuram_shanmugavel2
Contributor
0 Kudos

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

Share me your calculated column screenshot with error details.

Regards,

Muthuram

former_member344586
Participant
0 Kudos

Hi Muthuram,

below is the screenshot of the calculated column and error

Thanks!

muthuram_shanmugavel2
Contributor
0 Kudos

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
Participant
0 Kudos

Hi Muthuram,

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

Thanks!

former_member344586
Participant
0 Kudos

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
Contributor
0 Kudos

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
Participant
0 Kudos

Hi Muthuram,

I copied your expression, but same error.

Thanks!

former_member344586
Participant
0 Kudos

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
0 Kudos

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

Appreciate your response

lbreddemann
Active Contributor
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

They are empty string values

Former Member
0 Kudos

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