why is my numeric value changing to an asterisk?

  • i wrote this code that would take input of a year and a julian day and convert it to a calendar date.  for some reason it is changing the values for Day and Year to asterisks; but it is not doing this with the value for the Month, though all three variables are identical- as far as i can tell.  the code is below-  can anybody shed some light on this for me?  thanks

     

    d

     

     

    -- convert Julian Date to Calendar Date

    declare @Julian_Date int

    declare @Year int

    declare @Ld int

    declare @LeapYear bit

    declare @Month int

    declare @Day int

    declare @Month2 as char

    declare @Day2 as char

    declare @Year2 as char

    declare @Calendar_Date varchar(10)

    set @Year = 2004

    set @Julian_Date = 60

     -- 1)  Determine Leap Year

      if @Year % 4 = 0  -- Leap Year

          begin

        set @LeapYear = 1

        set @Ld = 1

       end

      else    -- Not A Leap Year

          begin

        set @LeapYear = 0

        set @Ld = 0

       end

     

     -- 2)  Get Month

      if @Julian_Date < 32

       begin 

        set @Month = '01' -- January

       end

            

          

     else    if @Julian_Date > 31 and @Julian_Date < (60 + @Ld)

       begin

        set @Month = '02' -- February

       end

           

           

     else    if @Julian_Date > (59 + @Ld) and @Julian_Date < (91 + @Ld)

       begin

        set @Month = '03' -- March

       end

           

           

     else    if @Julian_Date > (90 + @Ld) and @Julian_Date < (121 + @Ld)

       begin

        set @Month = '04' -- April

       end

           

           

     else    if @Julian_Date > (120 + @Ld) and @Julian_Date < (152 + @Ld)

       begin

        set @Month = '05' -- May

       end

           

           

     else    if @Julian_Date > (151 + @Ld) and @Julian_Date < (182 + @Ld)

       begin

        set @Month = '06' -- June

       end

           

           

     else    if @Julian_Date > (181 + @Ld) and @Julian_Date < (213 + @Ld)

       begin

        set @Month = '07' -- July

       end

           

           

     else    if @Julian_Date > (212 + @Ld) and @Julian_Date < (244 + @Ld)

       begin

        set @Month = '08' -- August

       end

             

     else    if @Julian_Date > (243 + @Ld) and @Julian_Date < (274 + @Ld)

       begin

        set @Month = '09' -- September

       end

             

     else    if @Julian_Date > (273 + @Ld) and @Julian_Date < (305 + @Ld)

       begin

        set @Month = '10' -- October

       end

             

     else    if @Julian_Date > (304 + @Ld) and @Julian_Date < (335 + @Ld)

       begin

        set @Month = '11' -- November

       end

             

     else    if @Julian_Date > (334 + @Ld) and @Julian_Date < (366 + @Ld)

       begin

        set @Month = '12' -- December

       end

     

     -- 3)  Get Day

      if @Month = '01'

       begin 

        set @Day = @Julian_Date

       end

      if @Month = '02'

       begin 

        set @Day = @Julian_Date - 31

       end

      if @Month = '03'

       begin 

        set @Day = @Julian_Date - (59 + @Ld)

       end

      if @Month = '04'

       begin 

        set @Day = @Julian_Date - (90 + @Ld)

       end

      if @Month = '05'

       begin 

        set @Day = @Julian_Date - (120 + @Ld)

       end

      if @Month = '06'

       begin 

        set @Day = @Julian_Date - (151 + @Ld)

       end

      if @Month = '07'

       begin 

        set @Day = @Julian_Date - (181 + @Ld)

       end

      if @Month = '08'

       begin 

        set @Day = @Julian_Date - (212 + @Ld)

       end

      if @Month = '09'

       begin 

        set @Day = @Julian_Date - (243 + @Ld)

       end

      if @Month = '10'

       begin 

        set @Day = @Julian_Date - (273 + @Ld)

       end

      if @Month = '11'

       begin 

        set @Day = @Julian_Date - (304 + @Ld)

       end

      if @Month = '12'

       begin 

        set @Day = @Julian_Date - (334 + @Ld)

       end

     

     -- 4)  Return Calendar Date

       set @Month2 = @Month

       set @Day2 = @Day

       set @Year2 = @Year

    print 'Month: ' + @Month2

    print 'Day: ' + @Day2

    print 'Year: ' + @Year2

      set @Calendar_Date = @Month2 + '/' + @Day2 + '/' + Right(@Year2, 2)

     

     

     

     

     

     

  • declare @Julian_Date int

    declare @Year int

    set @Year = 2004

    set @Julian_Date = 60

    Select DateAdd(dd,@Julian_DAte, Cast( @Year - 1  as char(4)) +'-12-31')  as CalendarDay


    * Noel

  • Take a look at BOL for CAST and CONVERT.

    When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table.

    ...as I have turned off the WYSIWYG editor here, I can't paste the table, so please look it up in BOL. Likely to be the cause of your problem.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • the thing is, i know how to get around this problem; but i want to know why it's happening in the first place. 

    the variables for Month, Day, and Year are all identical-, and all three accept their initial value assignments; but the Day and Year variables replace their second value assignments with asterisks, although each of those values is an integer just as were the initial values;  so i can't see why one variable would behave the way it is expected to, while the other two reject my attempts to re-assign them values.

  • Ready for an AHA effect?

    declare @Month2 as char

    declare @Day2 as char

    declare @Year2 as char

    is the same as

    declare @Month2 as char(1)

    declare @Day2 as char(1)

    declare @Year2 as char(1)

    Change it to

    declare @Month2 as char(2)

    declare @Day2 as char(2)

    declare @Year2 as char(4)

    you'll get

    Month: 2

    Day: 29

    Year: 2004

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • that did it.  thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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