converting Year and Month varchar to date

• SqlServerNinja

  Ten Centuries

  Points: 1238

  More actions

  January 31, 2014 at 1:09 pm

  #287877

  I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.

  Year Month Productname

  2013 11 ACB

  2013 11 CDE

  I would now like to add another column called date and store these Year Month as a date to my existing table

  Year Month ProductName Date

  2013 11 ACB 2013-11-01

  2013 11 CDE 2013-11-01

  Is there a way I can do it for all the columns of the existing table ??

  Please let me know

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 31, 2014 at 1:19 pm

  #1685189

  You can add a derived column on the table with a formula as used below:

  WITH SampleData([Year], [Month], Productname ) AS(

  SELECT '2013', '11', 'ACB' UNION ALL

  SELECT '2013', '11', 'CDE'

  )

  SELECT *, CAST( [Year] + [Month] + '01' AS date)

  FROM SampleData

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 31, 2014 at 3:34 pm

  #1685238

  Luis Cazares (1/31/2014)

  ---

  You can add a derived column on the table with a formula as used below:

  WITH SampleData([Year], [Month], Productname ) AS(

  SELECT '2013', '11', 'ACB' UNION ALL

  SELECT '2013', '11', 'CDE'

  )

  SELECT *, CAST( [Year] + [Month] + '01' AS date)

  FROM SampleData

  And make sure the column is a PERSISTED column so that you can index it and the column isn't recalculated every time you read it.

  --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)
  

• jdr.suporte

  SSC Rookie

  Points: 32

  More actions

  January 31, 2014 at 5:14 pm

  #1685258

  Try

  ALTER TABLE table

  ADD [Date] date;

  go

  UPDATE table

  set [Date]= DateFromParts(cast([Year] as int), cast([Month] as int), 1);

  go

  /*

  ALTER TABLE table

  DROP COLUMN [Year], [Month];

  */

  or

  ALTER TABLE table

  add [Date] as DateFromParts(cast([Year] as int), cast([Month] as int), 1) persisted;