CODEWITHSUNDEEP
sql-serverdatetimessisado.netetl
User5590
User5590

Reputation: 1435

Convert date DD.MM.YYYY hh:mm:ss to YYYY-MM-DD in SSIS

I am developing an SSIS package and it reads the data from SAP. I have created ADO.Net source which reads the data from ODBC connection.

From the SAP data, there is a table which has the date column and the format of the date value is 12.03.2014 00:00:00

Now I want to convert it into datetime format YYYY-MM-DD.

Can I do this in SQL command of ADO.Net source tool?

Upvotes: 3

Views: 2122

Answers (4)

Ricardo Logli Matsumura
Ricardo Logli Matsumura

Reputation: 1

I hope it also can help someone, cause I lost a bunch of time and got struggling with just get the Year-Month-Day Hour:Minute:Second, like "YYYY-MM-dd hh:mm:ss". In the learn.MS page It could not even give me a simple clue and how to do that. Here is what I figured out:

REPLACE((DT_WSTR, 10)(DT_DBDATE)GETDATE(),"-","-") + " " + (DT_WSTR, 10)DATEPART("hour", GetDate()) + ":" +  (DT_WSTR, 10)DATEPART("minute", GetDate()) + ":" +  (DT_WSTR, 10)DATEPART("second", GetDate())

Upvotes: 0

Khairul Alam
Khairul Alam

Reputation: 1336

Take a drived column component and convert the date as expected like following example:

(DT_STR,4,1252)DATEPART("yyyy",GETDATE()) + RIGHT("0" + "-" + (DT_STR,2,1252)DATEPART("mm",GETDATE()),2) + "-" + RIGHT("0" + (DT_STR,2,1252)DATEPART("dd",GETDATE()),2)

enter image description here

Upvotes: 0

Hadi
Hadi

Reputation: 37348

I don't really know how to achieve this using SQL Command using SAP driver but here are some useful infos

If you are working with DateTime Data Type , Datetime are not stored with their formats they are stored as Number OR string Values (The are many ways that date are stored (related to the data provider used); decimals or two integer , ...) .

For more info take a look at :

Date formats are related to your Regional , application , DBMS settings

If you want to show dates with other formats you have to change the related setting. Or you can convert it to String datatype with a specific format using a script component:

If Dates are stored as string

If the Date column is a string you can use the DateTime.ParseExact method in a script component. (assuming that outDate is the output column and inDate is the input column) 

using System;
using System.Globalization;


CultureInfo provider = CultureInfo.InvariantCulture;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.outDate = DateTime.ParseExact(Row.inDate,"dd.MM.yyyy HH:mm:ss",provider).ToString("yyyy-MM-dd");
}

for more info on this method you can refer to this links:

If dates are stored as dates

using System;
using System.Globalization;


CultureInfo provider = CultureInfo.InvariantCulture;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.outDate = Row.inDate.ToString("yyyy-MM-dd");
}

Upvotes: 1

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

--Yes you Can do it In SQL SERVER

Declare @Date Datetime
SET @Date='12.03.2014 00:00:00'

SELECT @Date=CONVERT(NVARCHAR(50),@Date,112)
SELECT @Date

Upvotes: 2

Related Questions