Use of CONCAT function in datetime column.

• jorge_vicente

  SSC Eights!

  Points: 983

  More actions

  February 14, 2017 at 10:40 am

  #320363

  Hi everyone,

  I have a column named "Date" with a type of "datetime" in SQL 2012.
  This is an example of the data that we have in the field:
  "2011-05-16 00:00:00.000"

  I am doing a GROUPING with a PIVOT table. I would like to use a column por every year-month. (Example: ,201612,201701,201702, ...)

  
SELECT CONCAT(DATEPART(YEAR,Date), IIF(DATEPART(MM,Date) <=9, '0', '') , DATEPART(MM,Date)) as YearMonth


  Finally, it works correctly, but I would like to know if this is the "correct" form.
  Could I do this from another way?

  Regards!

• Lowell

  SSC Guru

  Points: 323522

  More actions

  February 14, 2017 at 10:56 am

  #1928436

  There's more than one way to do that.
  in my case, the convert function would work nicely, if you include the optional code:
  SELECT CONVERT(varchar(10),getdate,112) , where you replace getdate() with your datetime column.
  casting it to varchar(10) strips the time out if it existed.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 14, 2017 at 12:16 pm

  #1928457

  Lowell - Tuesday, February 14, 2017 10:56 AM

  There's more than one way to do that.
  in my case, the convert function would work nicely, if you include the optional code:
  SELECT CONVERT(varchar(10),getdate,112) , where you replace getdate() with your datetime column.
  casting it to varchar(10) strips the time out if it existed.

  Actually, that format doesn't include time. Using a length of 6 would return year and month, and we don't need it to be variable.
  SELECT CONVERT(char(6),GETDATE(), 112)

  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

• jorge_vicente

  SSC Eights!

  Points: 983

  More actions

  February 16, 2017 at 9:37 am

  #1928889

  Thank you for the help!!

  This form works correctly with the "Date" column
  
SELECT CONVERT(varchar(06),Date,112)
200902
  It is the solution "200902"

  And the good way is that i don't have to put  the tricky part of IIF(DATEPART(MM,Date) <=9, '0', '') to recover the "0" in the months from 01-09.

  Thank you again for so fast answer

  Regards!