Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS convert to date issue Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:23 AM
Points: 21, Visits: 103
im developing an ssis package.

while im in the sql server , there is a field that contains a text string , for example:

December 13, 2008

and so on...

i want to convert it to 2008-12-13 00:00:00.000 or 2008-12-13 (its same for me)

so, in the sql server i can do it with this statement:

SELECT convert(datetime,myfiled, 107)

FROM ....

the question is , what is the equivalent statement to it within the SSIS 2008 ? (with derived column)

thank you
Post #1409552
Posted Monday, January 21, 2013 1:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:23 AM
Points: 1,135, Visits: 1,149
avishain (1/21/2013)
im developing an ssis package.

while im in the sql server , there is a field that contains a text string , for example:

December 13, 2008

and so on...

i want to convert it to 2008-12-13 00:00:00.000 or 2008-12-13 (its same for me)

so, in the sql server i can do it with this statement:

SELECT convert(datetime,myfiled, 107)

FROM ....

the question is , what is the equivalent statement to it within the SSIS 2008 ? (with derived column)

Why not just do it in your source SQL as you described above? Or is the datasource not from SQL Server?

If not, take a look at: http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html#!/2008/11/converting-strings-to-dates-in-derived.html

HTH,
Rob
Post #1409693
Posted Tuesday, January 22, 2013 12:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:23 AM
Points: 21, Visits: 103
the DS would be an XML file.... so i have to this within the ssis package :)

Post #1409830
Posted Tuesday, January 22, 2013 10:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:36 PM
Points: 162, Visits: 562
avishain (1/21/2013)
im developing an ssis package.

while im in the sql server , there is a field that contains a text string , for example:

December 13, 2008

and so on...

i want to convert it to 2008-12-13 00:00:00.000 or 2008-12-13 (its same for me)

so, in the sql server i can do it with this statement:

SELECT convert(datetime,myfiled, 107)

FROM ....

the question is , what is the equivalent statement to it within the SSIS 2008 ? (with derived column)

thank you





Here is expression you needed

(DT_STR,4,1252)YEAR((DT_DBDATE)"January 22, 2008") + "-" + (DT_STR,2,1252)MONTH((DT_DBDATE)"January 22, 2008") + "-" + (DT_STR,2,1252)DAY((DT_DBDATE)"January 22, 2008")
Post #1410168
Posted Tuesday, January 22, 2013 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:36 PM
Points: 162, Visits: 562
Here is expression you needed

(DT_STR,4,1252)YEAR((DT_DBDATE)"January 22, 2008") + "-" + (DT_STR,2,1252)MONTH((DT_DBDATE)"January 22, 2008") + "-" + (DT_STR,2,1252)DAY((DT_DBDATE)"January 22, 2008")
Post #1410169
Posted Tuesday, January 22, 2013 11:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 54, Visits: 331
type casting that column to dt_dbtimestamp should work
Post #1410173
Posted Wednesday, January 23, 2013 1:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 09, 2013 5:23 AM
Points: 21, Visits: 103
sqlbi.vvamsi (1/22/2013)
type casting that column to dt_dbtimestamp should work


Works like a charm :)


thank you
Post #1410384
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse