Use of CONCAT function in datetime column.

Question

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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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!