Convert DateTime to String in SSIS

• endayagilbert

  SSC-Addicted

  Points: 480

  More actions

  July 9, 2015 at 3:47 am

  #302222

  Hi,

  I just want to ask for help on how to convert Datetime to String in SSIS.

  >First Day (Previous Month)

  (DT_DATE)((DT_WSTR, 4)YEAR(DATEADD("mm", -1, GETDATE())) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("mm", -1, GETDATE())),2)+"-01")

  >Last Day (Previous Month)

  DATEADD("dd", -1, (DT_DATE)((DT_WSTR, 4)YEAR(GETDATE()) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2)+"-01"))

  Hoping for your replies.

  Thank you very much.

  Gilbert

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  July 9, 2015 at 4:25 am

  #1811230

  This converts a datetime to a string.

  (DT_WSTR, 30) GETDATE()

  ---

• endayagilbert

  SSC-Addicted

  Points: 480

  More actions

  July 9, 2015 at 4:36 am

  #1811231

  Hi,

  You mean like this?

  (DT_WSTR, 30) DATEADD("dd", -1, (DT_DATE)((DT_WSTR, 4)YEAR(GETDATE()) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2)+"-01"))

  I'm sorry. Just a newbie in SSIS.

  Thank you.

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  July 9, 2015 at 5:11 am

  #1811237

  If you will take a step back and tell me

  a) What you are trying to achieve and

  b) What format you want the result in

  I might be able to help you more.

  Your original post merely asked for help converting a datetime to a string and my response demonstrated that.

  ---

• endayagilbert

  SSC-Addicted

  Points: 480

  More actions

  July 9, 2015 at 5:15 am

  #1811238

  Hi,

  Thank you.

  What I want to achieve are:

  a) get the first day date of the previous month based on current date

  b) get the last day date of the previous month based on current date

  c) format will be in mm/dd/yyyy

  d) should be converted to string

  Thank you again

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  July 9, 2015 at 5:20 am

  #1811239

  endayagilbert (7/9/2015)

  ---

  Hi,

  Thank you.

  What I want to achieve are:

  a) get the first day date of the previous month based on current date

  b) get the last day date of the previous month based on current date

  c) format will be in mm/dd/yyyy

  d) should be converted to string

  Thank you again

  A single string? Like this?

  06/01/2015 06/30/2015

  ---

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  July 9, 2015 at 5:35 am

  #1811250

  First day of previous month:

  right("0" + (DT_STR, 2, 1252) MONTH( dateadd("mm",-1,getdate())),2) + "/01/"+ (DT_STR, 4,1252) year(dateadd("mm",-1,getdate()))

  ---

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  July 9, 2015 at 5:51 am

  #1811254

  Last day of previous month is this, but not in the format you want.

  (DT_WSTR, 40) dateadd("d",-1,Dateadd("m",DATEDIFF( "m", (DT_DBTIMESTAMP) "1900-01-01", getdate() ), (DT_DBTIMESTAMP) "1900-01-01"))

  I'll leave it to you to use DAY(), MONTH() and YEAR() on the above to produce the format you are after.

  ---