Dispaly the years&rem months when the col is int datatype
----------------------------------------------------
select cast((age1/12) as varchar(20)) +' yrs ' + cast((age1%12) as varchar(20)) + ' months' from age
when col is varchar
-------------------
select cast( (substring(age1,1,2)/12) as varchar(20)) + ' yrs ' +
cast( (substring(age1,1,2)%12) as varchar(20)) + ' months' from age
code when the Months are zero i.e sdn't display when the months are zero
------------------------------------------------------------------------
select AGeDet.years + AGeDet.age1 from
(
select cast(age1/12 as varchar(30)) + ' years ' years ,+ case age1%12
when 0 then ''
ELSE cast(age1%12 as varchar(10))+ ' months'
END AS age1
from age ) AGeDet
Below code used for preparing the Insert script for the Existing rows in a table
----------------------------------------------------------------------------------
Declare @qt as varchar(1000)
set @qt = ''''
print @qt
select 'insert into program values ',
ProgramID, ',', ProgramCode ,',', @qt+ Description + @qt from program
Script used to display the vertical rows as Horizantal rows by comma separated
------------------------------------------------------------------------------
declare @retstr varchar(8000)
select @retstr = COALESCE(@retstr + ',','') + cast(memno as varchar) from sample where sno=1
print @retstr
For updating the column by removing the chars 'September 2004' can be updated as sept 04 of a single col
--------------------------------------------------------------------------------------------------------
update #t1 set period = left(period,4) + ' ' + right(period,2) where Period like '%sept%'
Supply a Date and it Returns with the Month Last Date by considering the Year i.e if its Leap Year
---------------------------------------------------------------------------------------------------
declare @date datetime
set @date = '02/01/2004'
set @date = getdate()
--get first day of month
select convert(varchar(50),dateadd(m, datediff(m, 0, @date), 0),101) as hi
--get last day of month
select convert(varchar(50),dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -1),101) as enddate
