Details
Written by Josh
Category: Blog
Published: 23 October 2018
Hits: 4694
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.