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?
--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
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");
}
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)
