Date conversion

• walter grimes

  SSC Rookie

  Points: 49

  More actions

  October 18, 2005 at 7:18 am

  #188125

  I have three fields coming from an AS400 they are: year 4 digits, month 2 digits and day 2 digits,

  I want this to be a small date when I bring it into SQL2000 is there a function that will let me do that?

  Or how would I do that?

  Thanks

  Walter

   

• Papillon

  Hall of Fame

  Points: 3303

  More actions

  October 19, 2005 at 2:40 am

  #598166

  Hi!!!!

  If data is in XML format like as below then....

  declare @idoc int

  declare @doc varchar(1000)

  set @doc ='

  <ROOT  YYYY="1996"  MM="8" DD="11">

  </ROOT>'

  --Create an internal representation of the XML document.

  exec sp_xml_preparedocument @idoc OUTPUT, @doc

  -- SELECT stmt using OPENXML rowset provider

  SELECT convert(varchar(10),cast((CONVERT(VARCHAR(10),(YYYY + '/'+ MM + '/' + DD))) as datetime),1) ORDERDATE INTO #Temp

  FROM   (

  SELECT * FROM OPENXML (@idoc, '/ROOT',1)

           WITH (YYYY       varchar(4) ,

                 MM  varchar(2) ,

                 DD    varchar(2))

  ) P

  SELECT * FROM #Temp

  DROP TABLE #Temp

  But just confirm that.....here field is inserting in MM/DD/YY format....

  ---

  Regards,

  Papillon

• walter grimes

  SSC Rookie

  Points: 49

  More actions

  October 19, 2005 at 5:25 am

  #598181

  I get the data from the 400, I set up a job on sql2000 to qry the as400 via an odbc link and populate the sql2000 table with the data, I will try the SELECT convert(varchar(10),cast((CONVERT(VARCHAR(10),(YYYY + '/'+ MM + '/' + DD))) as datetime),1)  and see if that works.

  Thanks

  Walter

   

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  October 31, 2005 at 10:30 pm

  #601473

  This works...

  SELECT CAST(YYYY+MM+DD AS SMALLDATETIME)

  The reason why it works is because yyyymmdd is one of the (ISO) recognized datetime formats... no need for the slashes or anything...

  --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)
  Intro to Tally Tables and Functions