December 11, 2002 at 12:35 pm
Hello to all.
Here is the question:
I got this script to be run, it is working fine, but the month come back with only one digit when the month is less than 10.
Any suggestion will be appreciated
The sample code is:
DECLARE @cDate AS char(8)
DECLARE @Interval as int
SELECT @cDate = '20021225'
SELECT @Interval = -90
DECLARE @Temp as Varchar(12)
DECLARE @TMonth as Varchar(2)
DECLARE @TDay as Varchar(30)
DECLARE @TYear as Varchar(4)
DECLARE @T1 as Varchar(12)
DECLARE @TempDate as varchar(30)
DECLARE @TDate as varchar(30)
DECLARE @TDateOut as Varchar(8)
SELECT @Temp =
convert(varchar(30),DateAdd(d, @Interval,
SUBSTRING(@cDate, 5, 2) + '-' +
SUBSTRING(@cDate, 7, 2) + '-' +
SUBSTRING(@cDate, 1, 4)),112)
--- Do Day calculations
SELECT @T1 = CASE WHEN DATEPART(dw,@Temp) = 01 THEN DateAdd(d, 1, @Temp)
WHEN DATEPART(dw,@Temp) = 07 THEN DateAdd(d, 2, @Temp) ELSE @Temp END
select @Temp as '@Temp',@T1 as '@T1', month(@t1) as month1, datepart(mm,@t1)as 'month2'
-- Get the month in digits
SELECT @TMonth = CASE WHEN MONTH(@T1) = 1 THEN '01'
WHEN MONTH(@T1) = 2 THEN '02'
WHEN MONTH(@T1) = 3 THEN '03'
WHEN MONTH(@T1)= 4 THEN '04'
WHEN MONTH(@T1) = 5 THEN '05'
WHEN MONTH(@T1) = 6 THEN '06'
WHEN MONTH(@T1) = 7 THEN '07'
WHEN MONTH(@T1) = 8 THEN '08'
WHEN MONTH(@T1) = 9 THEN '09' ELSE MONTH(@T1) END
After it is run, the result is:
20020926Sep 26 2002 99
and should be:
20020926Sep 26 2002 0909
HELP!!!
December 11, 2002 at 12:41 pm
Add this to the very end of your script and run it.
print right('00' + @tmonth,2)
December 11, 2002 at 12:48 pm
Thank you. I added the print statement you recommended and it came back the same. What am I doing wrong?
December 11, 2002 at 12:58 pm
you can just add this select statement to your variable
select @Month = right(('0' + cast(datepart(mm, <yourdate>) as varchar(2), 2)
December 11, 2002 at 1:05 pm
Here try this:
select @Temp as '@Temp',@T1 as '@T1', substring(cast(100+month(@t1)as char(3)),2,3) as 'month1' , substring(cast(datepart(mm,@t1)+100 as char(3)),2,3) as 'month2'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 11, 2002 at 4:15 pm
The problem comes from the fact that month() returns an int not a char, as does datepart. You will have to cast to a char to get it to work like you want.
Other option are
select replicate('0', 2 - len(month(@t1))) + cast(month(@t1) as varchar(2)) as x
or
DECLARE @m1 AS VARCHAR(2)
SET @m1 = cast(month(@t1) as varchar(2)) -- do this explicitly as SQl versions don't always handle the same and may not in the future.
select replicate('0', 2 - len(@m1)) + @m1 as x
Also, being that you are using SQL 2000 it might be more helpfull to write whatever you decide on into a UDF (User Defined Function) that way you can reuse with much less code.
December 11, 2002 at 8:52 pm
quote:
Here try this:select @Temp as '@Temp',@T1 as '@T1', substring(cast(100+month(@t1)as char(3)),2,3) as 'month1' , substring(cast(datepart(mm,@t1)+100 as char(3)),2,3) as 'month2'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
I used to do this all the time back in the old assembler programming days. It works well and is obvious once you start thinking RIGHT instead of left.
December 12, 2002 at 6:11 am
I would use
SELECT @TMonth = REPLACE(STR(MONTH(@T1),2,0),' ','0')
but then thats me.
Far away is close at hand in the images of elsewhere.
Anon.
December 12, 2002 at 8:54 am
Thank you all. With all your input, I solve the issue and successfully implementd it. Thank you again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply