2

I get the Year and Month using this Query given below

select CAST(YEAR(GETDATE()) AS VARCHAR )+'0'+CAST(MONTH(GETDATE()) AS VARCHAR)

But for this Month is printing Correctly but for 10 month its printing 010 can any one help on this.

Dharma
  • 3,007
  • 3
  • 23
  • 38
Mayuran
  • 35
  • 3
  • http://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length – Tim Schmelter Sep 05 '14 at 11:59
  • The best way to get this should be SQL format using `SELECT CONVERT(VARCHAR(10),GETDATE(), 112)` You can take substring and remove the last 2 digits `SELECT SUBSTRING(CONVERT(VARCHAR(10),GETDATE(), 112), 0,7)` – Murtaza Sep 05 '14 at 12:08
  • I would say this is not duplicate because user is not requesting for the same, but we can suggest the better answer and optimize the query. – Murtaza Sep 05 '14 at 12:09
  • 1
    @Murtaza or just simply `CONVERT(VARCHAR(6), GETDATE(), 112)` – GarethD Sep 05 '14 at 12:10
  • yes @GarethD - That is also possible (i missed out that...) `SELECT CONVERT(VARCHAR(6),GETDATE(), 112)` this code will give the best result in efficient way! – Murtaza Sep 05 '14 at 12:16

1 Answers1

1

One method to eliminate the extraneous digit is with the RIGHT function:

SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR(2)), 2);
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71