1

I want to convert monthly_dates to quarter, but I'd like to path single digit numbers with 0s. I.e., monthly_date = '2015-01-01' In the SQL query I do: QUARTER(monthly_date)*3-2 AS varchar but that gives me '1' instead of '01'

Is there a way to make it give me '01', '04', '07' and '10'?

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
Dnaiel
  • 7,622
  • 23
  • 67
  • 126
  • 1
    Yes, but that depends on the SQL engine you use – juergen d Apr 21 '16 at 18:27
  • Possible duplicate of [Most efficient T-SQL way to pad a varchar on the left to a certain length?](http://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length) – Siyual Apr 21 '16 at 18:27
  • @juergend thanks. I use https://my.vertica.com/docs/7.0.x/PDF/HP_Vertica_7.0.x_SQL_Reference_Manual.pdf – Dnaiel Apr 21 '16 at 18:28

2 Answers2

0

Can you have quarter number bigger than 4?

Anyhow... you can pad numbers the way you want (be sure to check SQL Reference Manual > SQL Functions > Formatting Functions).

For example:

SQL> select to_char( 4 , '09' ) ;
 to_char 
---------
   04
SQL> select to_char( 12 , '09' ) ;
 to_char 
---------
   12
mauro
  • 5,730
  • 2
  • 26
  • 25
-1

I admit, this is a brute force method but I have used it in the past. Given these 4 variables, I can calculate the StartCalendarId with padded 0's

Declare @StartDate date
Declare @StartYear int
Declare @StartMonth int
Declare @StartDay int

Set @StartYear = Year(@StartDate)
Set @StartMonth = Month(@StartDate)
Set @StartDay = Day(@StartDate)

Set @StartCalendarId =
(CONVERT([int],
    (((CONVERT([varchar](4),@StartYear)+
    case when len(@StartMonth)=(1) then '0' else '' end)+
    CONVERT([varchar](2),@StartMonth))+
    case when len(@StartDay)=(1) then '0' else '' end)
    +CONVERT([varchar](2),@StartDay))
)
J Greene
  • 261
  • 1
  • 7