Convert date value

• pwalter83

  SSChampion

  Points: 14557

  More actions

  April 7, 2020 at 10:58 am

  #3740990

  Hi,

  I have a requirement to convert a date value like '7th March 2020'  to '2020-03-07'.

  However, the problem is the datatype for the date column is varchar and this needs to be achieved in SSIS.

  Can somebody please help on this ?

  Thanks.

• John Mitchell-245523

  SSC Guru

  Points: 148745

  More actions

  April 7, 2020 at 11:43 am

  #3740995

  What have you tried so far?  I imagine if you replace the "th ", "nd " or "st " with " ", the CONVERT function in SSIS would be able to handle it, wouldn't it?

  John

• BTylerWhite

  Default port

  Points: 1416

  More actions

  April 10, 2020 at 3:24 pm

  #3742015

  I've never much enjoyed building out these complex expressions in SSIS.

  John Mitchell-245523 wrote:

  What have you tried so far?  I imagine if you replace the "th ", "nd " or "st " with " ", the CONVERT function in SSIS would be able to handle it, wouldn't it?

  John

  John's suggestion is correct, also just make sure you throw in "rd" as well.

  I created a variable named "sDate" with a String Data type. It's value is set to "7th March 2020".

  I used the following (quite lengthy) expression to convert this into the "YYYY-MM-DD" string format:

  (DT_STR, 4, 1252) DATEPART("YEAR", (DT_DATE) REPLACE(REPLACE(REPLACE(REPLACE(@[User::sDate], "st", ""), "nd", ""), "rd", ""), "th", "")) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("MONTH", (DT_DATE) REPLACE(REPLACE(REPLACE(REPLACE(@[User::sDate], "st", ""), "nd", ""), "rd", ""), "th", "")), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("DAY", (DT_DATE) REPLACE(REPLACE(REPLACE(REPLACE(@[User::sDate], "st", ""), "nd", ""), "rd", ""), "th", "")), 2)

  Hopefully that helps as at least a starting point.

• Jeff Moden

  SSC Guru

  Points: 996455

  More actions

  April 10, 2020 at 4:11 pm

  #3742040

  Recreating such a thing in yet another "string format" would go against my grain.  Why not convert it to a proper DATE or DATETIME datatype?

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Forum FAQ