Date Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Date Error

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2011-03-09 : 20:43:26

Here is my sample data and field called "date_1"
29-SEP-10 12.00.00.000000000 AM
24-MAY-10 12.00.00.000000000 AM
11-MAR-03 12.00.00.000000000 AM
24-FEB-03 12.00.00.000000000 AM
17-JUN-03 12.00.00.000000000 AM
13-DEC-02 12.00.00.000000000 AM
30-DEC-02 12.00.00.000000000 AM

and my target field is "DATE"
and data type is "date time"

I think my target field can accept date like this
24-5-10
24-2-03 and so on.

Please help me out. Thanks.

latch
Yak Posting Veteran

62 Posts

Posted - 2011-03-11 : 11:29:25

Hi,

If the input date is coming from Flat file source and destination is also flat file then:

Take a script component:
Inside the input block:

string s = "null";
s = Row.DD.ToString();
s = s.Substring(0, 9);
string m = "null";
m = s.Substring(3, 3);

switch (m)
{
case "JAN": m = "01"; break;
case "FEB": m = "02"; break;
case "MAR": m = "03"; break;
case "APR": m = "04"; break;
case "MAY": m = "05"; break;
case "JUN": m = "06"; break;
case "JUL": m = "07"; break;
case "AUG": m = "08"; break;
case "SEP": m = "09"; break;
case "OCT": m = "10"; break;
case "NOV": m = "11"; break;
case "DEC": m = "12"; break;

}
Row.newdate = s.Substring(0, 3) + m + s.Substring(6, 3);

newdate is the output of type dt_str.

Thanks,
latch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources