USE [Cortex]
GO
/****** Object:  StoredProcedure [dbo].[SPNormalizeAddWhen]    Script Date: 03/04/2010 16:13:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[SPNormalizeAddWhen] 
As 
declare @colname nvarchar(20) 
set @colname='Normalized_Add_When' 
 
if not exists (select * from syscolumns where id=object_id('XXNkk_Fact_PnLSummary1') and name=@colname) 
    exec('alter table XXNkk_Fact_PnLSummary1 add [' + @colname  + '] datetime') 
 
 
declare @sql nvarchar(500) 
set @sql='update XXNkk_Fact_PnLSummary1 set [' + @colname + ']=cast(DATEPART(Hh,[_Addwhen]) as nvarchar(2)) + '':''  + cast(DATEPART(Mi,[_Addwhen]) as nvarchar(2)) ' 
exec(@sql) 

quote:

---

Originally posted by arunlgt

nothing yet? is it that tough?

---

IF OBJECT_ID(N'[dbo].[SPNormalizeAddWhen]') IS NOT NULL
        DROP PROCEDURE [dbo].[SPNormalizeAddWhen]

GO

CREATE PROCEDURE [dbo].[SPNormalizeAddWhen]
As
declare @colname nvarchar(20)
set @colname='Normalized Add_When'

if not exists (select * from syscolumns where id=object_id('Risk') and name=@colname)
    exec('alter table Risk add [' + @colname  + '] datetime')


declare @sql nvarchar(500)
set @sql='update Risk set [' + @colname + ']=cast(DATEPART(yyyy,[add when]) as nvarchar(4)) + ''-'' + cast(DATEPART(mm,[add when]) as nvarchar(2)) + ''-'' + cast(DATEPART(dd,[add when]) as nvarchar(2)) + '' '' + cast(DATEPART(Hh,[add when]) as nvarchar(2)) + '':''  + cast(round(DATEPART(Mi,[add when]),-1) as nvarchar(2)) '
print @sql
exec(@sql)
GO