how to Select getdate () in yyyy/M format ?

Question

Post reply

how to Select getdate () in yyyy/M format ?

sushantkatte

SSChasing Mays

Points: 647
More actions
January 13, 2016 at 5:33 pm

#310804

I want to select getdate() in the format yyyy/M. i tried to write a query as SELECT FORMAT(GETDATE(),'yyyy/M') but it is giving me error.I am a beginner in sql. how do i achieve yyyy/m format if there is only single digit month. eg. the query should give 2016/1 when there is only one digit month(it should not give 2016/01) and should give 2016/10 when the month is of two digits

Viewing 11 posts - 1 through 10 (of 10 total)

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

sushantkatte (1/13/2016)

I want to select getdate() in the format yyyy/M. i tried to write a query as SELECT FORMAT(GETDATE(),'yyyy/M') but it is giving me error.I am a beginner in sql. how do i achieve yyyy/m format if there is only single digit month. eg. the query should give 2016/1 when there is only one digit month(it should not give 2016/01) and should give 2016/10 when the month is of two digits

Works fine for me:

declare @date date;



set @date = '2015-01-06';

select FORMAT(@date,'yyyy/M'); -- yields 2015/1

set @date = '2015-10-06';

select FORMAT(@date,'yyyy/M'); -- yields 2015/10

What was the error you received?

Be careful anyway, FORMAT is very slow when compared to other date functions. CONVERT does not contain a style option for the format you need but even some basic string-building might be a better performing option.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 2

johnwalker10

SSCrazy Eights

Points: 9124

January 13, 2016 at 10:18 pm

#1851802

SELECT CONVERT(nvarchar(6), GETDATE(), 112)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Orlando Colamatteo

SSC Guru

Points: 182276

January 13, 2016 at 10:36 pm

#1851809

johnwalker10 (1/13/2016)

SELECT CONVERT(nvarchar(6), GETDATE(), 112)

That returns 201601 for days in January. The OP wants 2016/1 for January and 2016/10 for October. There is no style offered by CONVERT for that format.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 4

sushantkatte (1/13/2016)

I want to select getdate() in the format yyyy/M. i tried to write a query as SELECT FORMAT(GETDATE(),'yyyy/M') but it is giving me error.I am a beginner in sql. how do i achieve yyyy/m format if there is only single digit month. eg. the query should give 2016/1 when there is only one digit month(it should not give 2016/01) and should give 2016/10 when the month is of two digits

This might help, stuff the leading zero out if the month is less than 10.

😎

DECLARE @TDATE DATE = '2016-01-15';



SELECT STUFF(CONVERT(nvarchar(6), @TDATE, 112),5,1 - MONTH(@TDATE) / 10,'/');

SELECT CONVERT(VARCHAR(4),YEAR(@TDATE),0) + CONVERT(VARCHAR(4),MONTH(@TDATE),0);

SET @TDATE = '2015-11-20';

SELECT STUFF(CONVERT(nvarchar(6), @TDATE, 112),5,1 - MONTH(@TDATE) / 10,'/');

SELECT CONVERT(VARCHAR(4),YEAR(@TDATE),0) + CHAR(47) + CONVERT(VARCHAR(4),MONTH(@TDATE),0);

Output

-------



2016/1

-------

2015/11

Don't use the FORMAT function, it performs appallingly bad!

Edit: typo + missed the slash + alternative method

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Orlando Colamatteo (1/13/2016)

Be careful anyway, FORMAT is very slow when compared to other date functions. CONVERT does not contain a style option for the format you need but even some basic string-building might be a better performing option.

Eirikur Eiriksson (1/13/2016)

Don't use the FORMAT function, it performs appallingly bad!

Totally agree with that. In fact, it's 44 times slower than CONVERT. Guess I'll have to write and article on it. Getting tired of posting the code that proves 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)
Intro to Tally Tables and Functions

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Orlando Colamatteo

SSC Guru

Points: 182276

January 14, 2016 at 7:51 am

#1851919

Slay it. I was completely bummed when I learned how bad it was because functionally FORMAT is a breath of fresh air compared to other methods. Too bad it's essentially unusable.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 7

Jeff Moden (1/14/2016)

Orlando Colamatteo (1/13/2016)

Be careful anyway, FORMAT is very slow when compared to other date functions. CONVERT does not contain a style option for the format you need but even some basic string-building might be a better performing option.

Eirikur Eiriksson (1/13/2016)

Don't use the FORMAT function, it performs appallingly bad!

Totally agree with that. In fact, it's 44 times slower than CONVERT. Guess I'll have to write and article on it. Getting tired of posting the code that proves it. 🙂

IIRC I posted such a code the last time we had this discussion

😎

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Eirikur Eiriksson (1/14/2016)

Jeff Moden (1/14/2016)

Orlando Colamatteo (1/13/2016)

Be careful anyway, FORMAT is very slow when compared to other date functions. CONVERT does not contain a style option for the format you need but even some basic string-building might be a better performing option.

Eirikur Eiriksson (1/13/2016)

Don't use the FORMAT function, it performs appallingly bad!

Totally agree with that. In fact, it's 44 times slower than CONVERT. Guess I'll have to write and article on it. Getting tired of posting the code that proves it. 🙂

IIRC I posted such a code the last time we had this discussion

😎

True enough. If you hadn't, I would have. An article will save much time for both of us.

--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)
Intro to Tally Tables and Functions

nigel. SSChampion Points: 11636 More actions · Answer 9

Orlando Colamatteo (1/13/2016)

sushantkatte (1/13/2016)

I want to select getdate() in the format yyyy/M. i tried to write a query as SELECT FORMAT(GETDATE(),'yyyy/M') but it is giving me error.I am a beginner in sql. how do i achieve yyyy/m format if there is only single digit month. eg. the query should give 2016/1 when there is only one digit month(it should not give 2016/01) and should give 2016/10 when the month is of two digits

Works fine for me:

declare @date date;
set @date = '2015-01-06'; select FORMAT(@date,'yyyy/M'); -- yields 2015/1 set @date = '2015-10-06'; select FORMAT(@date,'yyyy/M'); -- yields 2015/10

What was the error you received?

Be careful anyway, FORMAT is very slow when compared to other date functions. CONVERT does not contain a style option for the format you need but even some basic string-building might be a better performing option.

A quick test shows that (on a 1 million row table) even this



SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/' + CAST(MONTH(GETDATE()) AS VARCHAR(2))

is considerably quicker than FORMAT

Results

(1000000 row(s) affected)

Table '#A1E46162'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Using CAST

SQL Server Execution Times:

CPU time = 312 ms, elapsed time = 325 ms.

Table '#A1E46162'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Using FORMAT

SQL Server Execution Times:

CPU time = 9266 ms, elapsed time = 9764 ms.

Nigel
How to post questions

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 10

nigel. (1/15/2016)

Orlando Colamatteo (1/13/2016)

sushantkatte (1/13/2016)

I want to select getdate() in the format yyyy/M. i tried to write a query as SELECT FORMAT(GETDATE(),'yyyy/M') but it is giving me error.I am a beginner in sql. how do i achieve yyyy/m format if there is only single digit month. eg. the query should give 2016/1 when there is only one digit month(it should not give 2016/01) and should give 2016/10 when the month is of two digits

Works fine for me:

declare @date date;
set @date = '2015-01-06'; select FORMAT(@date,'yyyy/M'); -- yields 2015/1 set @date = '2015-10-06'; select FORMAT(@date,'yyyy/M'); -- yields 2015/10

Thanks for the test Nigel, your findings are very much inline with what I would expect.

😎

What was the error you received?

Be careful anyway, FORMAT is very slow when compared to other date functions. CONVERT does not contain a style option for the format you need but even some basic string-building might be a better performing option.

A quick test shows that (on a 1 million row table) even this

SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/' + CAST(MONTH(GETDATE()) AS VARCHAR(2))

is considerably quicker than FORMAT

Results

(1000000 row(s) affected)

Table '#A1E46162'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Using CAST

SQL Server Execution Times:

CPU time = 312 ms, elapsed time = 325 ms.

Table '#A1E46162'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Using FORMAT

SQL Server Execution Times:

CPU time = 9266 ms, elapsed time = 9764 ms.