Be careful when converting months to strings in SQL

This is a public service announcement:

It is critical that when you extract the month from a date with a two digit conversion (between October and December) and put it into a variable, you must define the length of the variable, or it will truncate the second digit.

If you do not, it will return a 1 and not the correct digit value.

Running this code:

DECLARE @ldBegDate AS DATE = '10-09-2018'
DECLARE @inline_month_no_len AS VARCHAR = Ltrim(Rtrim(Str(Month(@ldBegDate))))
DECLARE @inline_month_w_len AS VARCHAR(2) = Ltrim(Rtrim(Str(Month(@ldBegDate))))
DECLARE @inline_month_no_trim AS VARCHAR = Cast(Month(@ldBegDate) AS VARCHAR)
DECLARE @inline_month_no_trim_w_len AS VARCHAR(2) = Cast(Month(@ldBegDate) AS VARCHAR)
DECLARE @the_right_way AS VARCHAR(2) = Cast(Datepart(mm, @ldBegDate) AS VARCHAR)

PRINT '@ldBegDate = ' + Cast(@ldBegDate AS VARCHAR(max))
PRINT '@inline_month_no_len = ' + @inline_month_no_len
PRINT '@inline_month_w_len = ' + @inline_month_w_len
PRINT '@inline_month_no_trim = ' + @inline_month_no_trim
PRINT '@inline_month_no_trim_w_len = ' + @inline_month_no_trim_w_len
PRINT '@the_right_way = ' + @the_right_way
PRINT 'No Variable = ' + Ltrim(Rtrim(Str(Month(@ldBegDate))))

Produces this output.

@ldBegDate = 2018-10-09
@inline_month_no_len = 1
@inline_month_w_len = 10
@inline_month_no_trim = 1
@inline_month_no_trim_w_len = 10
@the_right_way = 10
No Variable = 10

You've been warned.

Facebook Social Comments

Home

Be careful when converting months to strings in SQL