Split DATE formatted 'YYYY-MM-DD' into Concatenated YEAR and MO field

Question

Split DATE formatted 'YYYY-MM-DD' into Concatenated YEAR and MO field

quinn.jay

SSCertifiable

Points: 5505
More actions
June 30, 2017 at 10:12 am

#324255

Hello, I have a date field in the DATE in the format of 'YYYY-MM-DD' and how it is displayed, and I'm trying to split our Year and Month and concatenate into 'YYYYMM' format. I've tried DATEPART, DATEADD and other variations and I'm not quite getting the correct result or error.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Bill Talada SSChampion Points: 11954 More actions · Answer 1

It is incorrect to talk about a format for a date column itself. A date is always stored in a binary form with no display format associated with it.
SELECT convert(varchar(6),SYSDATETIME(),112);

quinn.jay SSCertifiable Points: 5505 More actions · Answer 2

Bill Talada - Friday, June 30, 2017 10:20 AM
It is incorrect to talk about a format for a date column itself. A date is always stored in a binary form with no display format associated with it.
SELECT convert(varchar(6),SYSDATETIME(),112);

where would i insert the date field in question to convert, and would I want to name it 'as colname'?

Bill Talada SSChampion Points: 11954 More actions · Answer 3

SELECT t.name, convert(varchar(6),t.create_date,112) AS MyDateCol FROM sys.tables t;

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

Jason A. Long

SSC-Insane

Points: 23786

June 30, 2017 at 10:40 am

#1948977

What is the actual data type of your "date field"?

quinn.jay SSCertifiable Points: 5505 More actions · Answer 5

Bill Talada - Friday, June 30, 2017 10:33 AM
SELECT t.name, convert(varchar(6),t.create_date,112) AS MyDateCol FROM sys.tables t;

LEFT(CONVERT(char(8), datecolname, 112), 6) AS YearMo

This does it for me, and so does the above

Thank you

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

quinn.jay - Friday, June 30, 2017 10:41 AM
Bill Talada - Friday, June 30, 2017 10:33 AM
SELECT t.name, convert(varchar(6),t.create_date,112) AS MyDateCol FROM sys.tables t;
LEFT(CONVERT(char(8), datecolname, 112), 6) AS YearMo
This does it for me, and so does the above
Thank you

You don't need the LEFT if you convert to a 6-chars long string. 😉

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