Need to get YYYYMM from date ?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Need to get YYYYMM from date ?

Need to get YYYYMM from date ?

Rate Topic

Display Mode

Topic Options

Author

Message

KumarSQLDBA

Posted Friday, September 14, 2012 8:53 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 29, 2013 4:03 AM
Points: 81, Visits: 824

Hi Guys,

Please help me to get YYYYMM from Getdate()?
I have tried the below query.But it gives 20129 only not 201209 format?

ANy help will be appreciated!!!!!!!!!

Regards,
Kumar
http://www.sqldbatask.blogspot.com/

Post #1359362

Lynn Pettis

Posted Friday, September 14, 2012 8:57 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,589, Visits: 27,394

KumarSQLDBA (9/14/2012)

Hi Guys,

Please help me to get YYYYMM from Getdate()?
I have tried the below query.But it gives 20129 only not 201209 format?

ANy help will be appreciated!!!!!!!!!

SELECT cast(year(getdate()) as char(4)) + right('0' + cast(month(getdate()) as varchar), 2);

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1359369

GSquared

Posted Friday, September 14, 2012 8:58 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

SELECT CONVERT(CHAR(6), GETDATE(), 112);

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1359372

KumarSQLDBA

Posted Friday, September 14, 2012 9:08 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 29, 2013 4:03 AM
Points: 81, Visits: 824

Million times thanks for the help from 'GSquared'.

It got working .!!!!!!!! Smile

Regards,
Kumar
http://www.sqldbatask.blogspot.com/

Post #1359389

Jeff Moden

Posted Friday, September 14, 2012 3:18 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771

KumarSQLDBA (9/14/2012)

Million times thanks for the help from 'GSquared'.

It got working .!!!!!!!! Smile

The next question is... do you understand why it works???

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1359683

peacesells

Posted Friday, September 14, 2012 11:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 AM
Points: 144, Visits: 697

I really appreciate the way how most of the frequent SQL gurus at SQL central try to feed the concept instead of just providing the answers.

Post #1359779

balajisrm

Posted Saturday, September 15, 2012 8:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 1:07 PM
Points: 26, Visits: 88

But this one is not working for SELECT CONVERT(CHAR(6), '2012-09-15', 112);

i mean When we provide a hard coded date.

Post #1359809

Jeffrey Williams 3188

Posted Saturday, September 15, 2012 1:35 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

balajisrm (9/15/2012)

But this one is not working for SELECT CONVERT(CHAR(6), '2012-09-15', 112);

i mean When we provide a hard coded date.

Because that is not a hard-coded date. It is a hard-coded string defined as CHAR(10) being converted to another string defined as CHAR(6).

Modify it to this:

SELECT convert(char(6), cast('2012-09-15' As date), 112);

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Post #1359841

GSquared

Posted Monday, September 17, 2012 6:43 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

balajisrm (9/15/2012)

But this one is not working for SELECT CONVERT(CHAR(6), '2012-09-15', 112);

i mean When we provide a hard coded date.

As mentioned, you need to convert that string to a date first.

But, even better yet, follow the practice of hard-coding dates following the ISO standard. YYYYMMDD, without hyphens. That avoids issues with localization and conversion.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1360160

« Prev Topic | Next Topic »

Permissions