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