5/30/2018

Exclude 0 from value in SQL Server


Exclude 0 from value in SQL Server

 

Create Table #TempData

(

ColumnData Varchar(20)

)

 

Insert into #TempData values('063506038')

Insert into #TempData values('0063506038')

Insert into #TempData values('00063506038')

Insert into #TempData values('00000063506038')

 

 

Select * from #TempData

 

Select REPLACE(LTRIM(REPLACE( ColumnData,'0',' ')),' ','0')  From #TempData

Format Date in SSIS MM-DD-YYYY or MM/DD/YYYY DD-MM-YYYY or DD/MM/YYYY


Format Date in SSIS MM-DD-YYYY or MM/DD/YYYY DD-MM-YYYY or DD/MM/YYYY

Date Format MM-DD-YYYY:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

+"-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"-"+

(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

Date Format MM/DD/YYYY:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

+"/"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"/"+

(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

Date Format DD-MM-YYYY:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"-"+

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

+"-"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )

Date Format DD/MM/YYYY:

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"/"+

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)

+"/"+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )