cancel
Showing results for 
Search instead for 
Did you mean: 

Need to change the date format

Former Member
0 Kudos

Hi,

I need to load the data from MS SQL through DB Connect. the source field date format is 2010-01-20 00:00 ( this is MSSQL data type - "smalldatetime") and required query format is 06-Feb-10.

So where we can change the format. in the query level, or at the info provider level.

1. If at the info provider level Please provide me the steps.

2. If at the query level. please provide me the steps to do.

what is the best option?

thanks in advance

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

thank u very much

Former Member
0 Kudos

Hi,

In the update write an field routine to convert date from the source format to destination format.

Use the FM: CONVERSION_EXIT_SDATE_INPUT to convert the dates

or

use the below code.

DATA : wa_date(12),

wa_day(2),

wa_mth(3),

wa_yer(4),

wa_date1(8), "yyyymmdd

wa_mnr(2).

wa_date = Source_structure-date1 // Here pass your Date field

SPLIT wa_date AT '-' INTO wa_day

wa_mth

wa_yer.

TRANSLATE wa_mth TO UPPER CASE.

SELECT SINGLE mnr FROM t247 INTO wa_mnr WHERE ktx = wa_mth

AND spras = sy-langu.

CONCATENATE wa_yer wa_mnr wa_day INTO wa_date1.

pass wa_date1 to return statment.

or

Use the custom FM to convert date.Find the link

http://wiki.sdn.sap.com/wiki/display/Snippets/FUNCTIONMODULEFORCONVERTINGDATEINTOTHEGIVENFORMAT

Regards,

Shiva Kumar G.C

Edited by: Shivu Kumar on Feb 15, 2011 1:28 PM

Former Member
0 Kudos

Hi,

Just to point out that certain languages cannot specify a short month name correctly in 3 characters (Russian is one for example),  and therefore T247 cannot be used to get the short names for a Russian month name.

former_member188011
Active Contributor
0 Kudos

Hi:

For the recommended steps, please refer to SAP Note 512739 - "BW external DB Connect for MS SQLServer"

Regards,

Francisco Milán.

Former Member
0 Kudos

Hi Milan,

thanks for our reply milan, I dont have the access for service market place, and no one here to help me.

Thanks

former_member188011
Active Contributor
0 Kudos

Summary

Symptom

Using the external database connect, you intend to connect an external MS SQLServer database to a BW with release level 3.0B or higher, in the form of a source system. The following note describes

- what prerequisites apply,

- what problems may occur,

- which of these problems can be solved.

This note is updated regularly.

Other terms

Rsdbc, DATES, TIMES, DBCON, DBCONNECT, Source System

Database Datasource Infosource

Reason and Prerequisites

I. General

II. Supported BW/Basis versions

III. Possible problem areas

I. General

When you implement the abovementioned functions you also have the option of loading data to a BW system from any MS SQL server database. Unlike an MS SQL server database provided by SAP AG, you can configure and use this database in any way as regards MS SQL server functions. This can result in constellations that cannot be used directly via external database access. A satisfactory customer-specific solution can be worked out for the majority of these scenarios.

The work involved in this case is in the area of consulting and not in the area of support.

To implement this type of connection, you require know-how and experience in the use of the source database (in this case, the MS SQL server), particularly in the following areas:

- Tools

- DB specific functions.

- DB specific functions required.

Knowledge of the source application is also essential to ensure that semantically relevant data reaches the BW system.

former_member188011
Active Contributor
0 Kudos

Solution

II. Supported BW/Basis versions

>= 3.0B / >= 6.20

III. Possible problem areas

a) Use of data types

1. Datetime in source system tables

The Datetime DB data type and similar (timestamp, smalldatetime) are not used in SAP installations.

This type of data cannot be transferred consistently into the BW system without additional activities. Solution:

You must create a view in the source system to transform the data. Since a field of the type DateTime actually represents two SAP Basis types (DATES, TIMES), you must split it into a maximum of two fields, as appropriate: create view <VIEWNAME> as

select

VD_1_D = convert(varchar(4), datepart(yyyy, d_1))#

+ case len(convert(varchar(2), datepart(mm, d_1)))

when 1 then '0' + convert(varchar(1), datepart(mm, d_1))

else convert(varchar(2), datepart(mm, d_1))

end

+ case len(convert(varchar(2), datepart(dd, d_1)))

when 1 then '0' + convert(varchar(1), datepart(dd, d_1))

else convert(varchar(2), datepart(dd, d_1))

end,

VT_1_T = case len(convert(varchar(2), datepart(hh, t_1)))

when 1 then /* Hour Part of TIMES */

case convert(varchar(2), datepart(hh, t_1))

when '0' then '24' /* Map 00 to 24 ( TIMES ) */

else '0' + convert(varchar(1), datepart(hh, t_1))

end

else convert(varchar(2), datepart(hh, t_1))

end

+ case len(convert(varchar(2), datepart(mi, t_1)))

when 1 then '0' + convert(varchar(1), datepart(mi, t_1))

else convert(varchar(2), datepart(mi, t_1))

end

+ case len(convert(varchar(2), datepart(ss, t_1)))

when 1 then '0' + convert(varchar(1), datepart(ss, t_1))

else convert(varchar(2), datepart(ss, t_1))

end

from <TABLENAME>

former_member188011
Active Contributor
0 Kudos

The t_1 Basis field in the table is converted into a DATES field VT_1_D and a TIMES field VT_1_T. During the generation of the VT_1_T field, the value for 00:00 hours is also converted to 24:00 hours. This conversion may be omitted if not required.

2. Float data type

In accordance with the IEEE standard regulations, MS SQL Server supports a value range of 1x10E-307 to go- 1x10E308.

This also applies to the corresponding ABAP data type.

However, the BW database in question may restrict this. Depending on the system, a restriction to 1x10E-25 is possible.

3. smallint data type

Currently, the 'smallint' data type cannot be mapped to a DDIC data type without problems.

Therefore, we recommend that you convert it to the 'int' data type in the extraction view:

..., convert(int, <ColumnName>) as <ExtractionName> ...

4. nvarchar data type

The length information displayed is twice as big as the number of characters you specified when you created the column.

The upper()function delivers a return value of the nvarchar type.

b) Writing names in the source system

Since the R/3 kernel used in BW can only process table and column names written in upper case, on the source system, you must create DB views that convert the original names in accordance with this rule.

c) Code page and sort sequence of the source system

R/3 kernel-based systems such as BW basically assume that the database being used was created using code page cp850 and with the sort sequence 'bin'.

The configuration of the source system may differ.

If the sort sequence is different, operations for string search (like) and area search (between, >, <) on character fields may return different results. Solution: There is currently no solution.

If you use multibyte code pages in the source system to save data with character sets of more than 256 characters (Kanji, Hiragana, Korean, Chinese and so on), the characters may be corrupted as a result.

Solution: There is currently no solution.

d) Authorizations and visible objects

Only the DB objects (tables and views), which directly belong to the database user who logged on, are visible.

You cannot use a login that belongs to the Sysadmin server role,

(for example 'sa').

To avoid problems loading data, proceed as follows:

1. Create a special login for the extraction.

You should add this login to the db_ddladmin database role in the corresponding database. This generates a similar DB user at database level.

The user you use must have the 'CREATE VIEW' authorization at least. This is contained in the role mentioned above.

2. For the extraction, you should only use DB views that were created under this DB user. (refer also to type conversion, naming convention and so on)

3. Assign the necessary authorizations (SELECT at least) to the selected user in the source tables, or the columns of these tables.

4. Use the specially created login in the logon information when you set up the DB source system in BW.

Former Member
0 Kudos

Thank U Milan,

Again thanks for your quick reply, our client already created views and load the data up to Datasource ( PSA tables) from there i have to load data to respective DSO and cubes, So i need to change the date format as per the client requirement. so where should i need to write the routines or settings.

former_member188011
Active Contributor
0 Kudos

Hi:

Check with your Client to see if it is possible to modify the views that are already in place and create new ones according to the recommendation on the SAP Note mentioned before. Unfortunately in this scenario you'd need to delete the data from the PSAs and reload it again.

Regards,

Francisco Milán.