topic change date format in select statement in Application Development and Automation Discussions

change date format in select statement

shobhan_rana — Mon, 30 May 2022 08:34:20 GMT

Hi all,

i want to convert ERDAT to YYYY-MM-DD format directly in select statement in Database - MSSQL.

Date format YYYYMMDD to YYYY-MM-DD. is it possible?

Regards

Re: change date format in select statement

Sandra_Rossi — Mon, 30 May 2022 10:13:22 GMT

Of course but the solution depends on the context of your development. Please clarify.

Re: change date format in select statement

FredericGirod — Mon, 30 May 2022 10:54:25 GMT

so strange to require a date format in a SELECT statement

Re: change date format in select statement

shobhan_rana — Mon, 30 May 2022 14:06:05 GMT

It works like follows but too late to do it in outside select.

CONCATENATE ls_out-erdat+0(4)

ls_out-erdat+4(2)

ls_out-erdat+6(2) INTO ls_out-erdat SEPARATED BY '-'.

Re: change date format in select statement

FredericGirod — Mon, 30 May 2022 14:07:44 GMT

but ERDAT is type Date ? or type CHAR10 ?

Re: change date format in select statement

shobhan_rana — Mon, 30 May 2022 14:18:55 GMT

Context is as follow. It is a normal select like ::

ERDAT ist 10 CHAR long.

REPORT ZCONTEXT.

Select single

CAST( erdat AS CHAR( 10 ) ) AS erdat,

from KNA1.

I just want to place "-" in between the year, month and date inside the select statement. so that it is JSON compatible date => YYYY-MM-DD

Thanks

Re: change date format in select statement

Sandra_Rossi — Mon, 30 May 2022 16:23:19 GMT

You have the following SQL functions:

CONCAT (also &&)
LEFT, SUBSTRING, RIGHT

Re: change date format in select statement

Jelena_Perfiljeva — Tue, 31 May 2022 18:01:20 GMT

Sorry, this is really rather RTFM question that is impossible to answer without knowing technical details. You've mentioned it's MS SQL, so I'm guessing it's an older system and CDS views are not applicable in your case.

Look up documentation for SELECT statement for your ABAP version (use F1). All the functions will be described there, I doubt anyone can tell you something different here. There is also Native SQL option (which is covered in documentation) but I doubt it'll help or would be a good idea.

I'm curious about the context though and why is it important to do this in SELECT statement directly. I doubt it'll give you any "code pushdown" benefits.