How to add leading zeros to my data?

I have a varchar column that has variations of following data: WKKT-FM, 2/21 WKKT-FM, 1/24-2/14 WKKT-FM, 3/14-3/21, 4/11-4/18 IKKT-FM, 12/29-1/12, 1/26-2/09, 6/01-6/15

And so on. I need to place leading zeros in front of days and months. As you can see some of them already have leading zeros.

I tried looking for #/ or -#, but I have to account for some values that already have the leading zero.

A function would be preferable as I have to get this field via the SELECT statement.

What is an effective way to accomplish this?

how to connect remote MSSQL server via PHP

steps for connect remote mssql server 2005 using PHP but getting follwing error undefined function mssql_connect()

Inserting an image to sql server from another computer

How do I insert an image to an sql server from a client computer?

INSERT INTO imagetable (imagecolumn)
SELECT     BulkColumn
FROM         OPENROWSET(BULK 'C:\Image.png', Single_Blob) AS img

I'm getting this error and after searching i found out it's because the sql server is looking for the image path in the server machine. My imagecolumn is a varbinary(MAX) format.

Cannot bulk load because the file "C:\Image.png" could not be opened. Operating system error code 3(The system cannot find the path specified.)

Moving from SQL 2005 to 2012

Here is my Stored procedure written in SQLServer 2005. Now we are moving to SQL2012 and getting bunch of errors where I used *= . I know I have to use LEFT OUTER JOIN syntax, but somehow I am stumped. Can someone please help me ? Also getting error on where I used ABS in where clause.

These 2 lines in question in WHERE CLAUSE BELOW
currhold.current_hold__001 *= #tmp_transac.current_hold__001 and
((abs(t_quantity_c)> 0.01 and #tmp_transac.current_hold__001 is null) or

Full SP is here
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_getEPMData_GFS]
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#factortemp','u') IS NOT NULL

BEGIN

DROP TABLE #factortemp

END


IF OBJECT_ID('tempdb..#transactemp','u') IS NOT NULL

BEGIN

DROP TABLE #transactemp

END

select *  into #transactemp from transac where tran_type in ('BUY','SHORT')


select curr_factor,cusip into #factortemp 
from mbs_fact A
where A.factor_date = 
(select max(B.factor_date) from mbs_fact B 
where B.cusip = A.cusip --and B.factor_date <= '10/30/2008'
group by B.cusip)

IF OBJECT_ID('tempdb..#pricetemp','u') IS NOT NULL

BEGIN

DROP TABLE #pricetemp

END

Create TABLE #pricetemp
(price numeric(19,8),cusip varchar(20),price_source varchar(20),Max_Price_Date smalldatetime)


IF OBJECT_ID('tempdb..#pricetemp1','u') IS NOT NULL

BEGIN

DROP TABLE #pricetemp1

END

IF OBJECT_ID('tempdb..#pricetemp2','u') IS NOT NULL

BEGIN

DROP TABLE #pricetemp2

END

select cusip, max(price_date) as MX_pxdate 
INTO #pricetemp1
from pricing 
group by cusip 
order by 1

SELECT A.cusip,B.Mx_pxdate, count(*) as dupcount 
INTO #pricetemp2
FROM pricing A, #pricetemp1 B 
WHERE A.cusip = B.cusip
AND A.price_date = B.MX_pxdate 
group by A.cusip,B.Mx_pxdate


insert into #pricetemp
select A.price,A.cusip,A.price_source,B.MX_pxdate
from pricing A, #pricetemp2 B
where A.price_date = B.MX_pxdate
AND A.cusip = B.cusip
AND B.dupcount = 1

insert into #pricetemp
select A.price,A.cusip,A.price_source,B.MX_pxdate 
from pricing A, #pricetemp2 B
where A.price_date = B.MX_pxdate
AND A.cusip = B.cusip
AND A.price_source='MANUAL'
AND B.dupcount = 2


IF OBJECT_ID('tempdb..#tmp_transac','u') IS NOT NULL
BEGIN
DROP TABLE #tmp_transac
END

select distinct current_hold__001, sum(abs(transac.total_amount)) as total_amount,broker
into #tmp_transac
from transac
where setl_y_n = 'N'
and replace(tran_type, ' ', '') in 
(select distinct replace(tran_type, ' ', '') as tran_type from ut_transac_types where amount_sign = 'POSITIVE') 
group by current_hold__001,broker



declare @System_Date smalldatetime
Select @System_Date = Convert(varchar(12),dbo.uf_getGLDate(),112)


--ISB
IF OBJECT_ID('tempdb..#tmpISB','u') IS NOT NULL

BEGIN

DROP TABLE #tmpISB

END

select distinct
     iss.user_desc1 as Issuer_ISB, 
     Guarantor.user_desc1 as Guarantor_ISB, 
     Security.Cusip  
     into #tmpISB
     from Security 
     left outer join Issuer Iss ON (Security.issuer = Iss.issuer ) 
    left outer join Issuer Guarantor ON (Security.Guarantor = Guarantor.issuer ) 


--

-----Existing EPM on Top
SELECT case 
when #factortemp.curr_factor = 0.00 then 0.00 
when #factortemp.curr_factor is null then 0.00 
else #factortemp.curr_factor end as curr_factor,
case 
when #pricetemp.price = 0.00 then 100.000000000 
when #pricetemp.price is null then 100.000000000 
else #pricetemp.price end as price,
replace(security.description1,',',' ') as description1,security.isin,portfoli.MGR1_TITLE as branch,
currhold.portfolio,replace(currhold.sec_type,' ','') as sec_type,
Convert(float,currhold.CURRENT_HOLD__001)*10000 as Trade_num,
currhold.cusip,
securtyp.prn_curr,
Chartacc.securities,
securitiesamt=case When SECURTYP.SEC_TYPE='RESIDCERT' Then Convert(decimal(18,2),CURRHOLD.GORIG_COST_C)
                        else Convert(decimal(18,2),CURRHOLD.T_QUANTITY_C)
End,
chartacc.INT_DUE_ACCRUED,
INT_DUE_ACCRUEDAmt = (CURRHOLD.T_ACCR_INTRST_C + CURRHOLD.T_INT_DIV_DUE_C),
chartacc.Discount,
DiscountAmt =  case When SECURTYP.SEC_TYPE='RESIDCERT' Then 0
                        When (CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C) > 0 THEN 0 
                        ELSE Convert(decimal(18,2),CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C)
End,
chartacc.Premium,
PREMIUMAmt=case    When SECURTYP.SEC_TYPE = 'RESIDCERT' Then 0
                   When (CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C) < 0 THEN 0 
                   ELSE Convert(decimal(18,2),CURRHOLD.GBOOK_VALUE_C-CURRHOLD.T_QUANTITY_C)
End,
chartacc.UNRLZD_GL_ASSET,
UNRLZD_GL_ASSETAmt=isnull(Convert(decimal(18,2),(CURRHOLD.GMARKET_VALUE_C - CURRHOLD.GBOOK_VALUE_C)),0),
chartacc.UNRLZD_GL_INC,
UNRLZD_GL_INCAmt = isnull(Convert(decimal(18,2),(CURRHOLD.GBOOK_VALUE_C - CURRHOLD.GMARKET_VALUE_C)),0),
chartacc.INTEREST_INCOME,
Interest_incomeamt= Convert(decimal(18,2),CURRHOLD.GAMZ_YTD_P+CURRHOLD.T_ID_EARN_YTD_P),
chartacc.RECVABLE_BROKER,
RECVABLE_BROKERAmt=
case when CURRHOLD.STATUS in ('SELL','SELL TBA') and (CURRHOLD.GSETTLE_DATE) >= dbo.uf_getGLDate()
then isnull((CURRHOLD.GORIG_COST_C)-(CURRHOLD.T_DUE_ACCR_CY_C),0)

else isnull(#tmp_transac.total_amount, 0) -- - isnull(seccash.prin_amount, 0)
end,
chartacc.PAYABLE_BROKER,
PAYABLE_BROKERAmt=
case when CURRHOLD.STATUS in ('BUY','BUY TBA') and (CURRHOLD.GSETTLE_DATE) >= dbo.uf_getGLDate() 
then isnull(-(CURRHOLD.GORIG_COST_C)-(CURRHOLD.T_DUE_ACCR_CY_C),0)

else 0
end,
CurrStatus=Case when currhold.status in ('BUY','SELL') AND currhold.closed_date > dbo.uf_getGLDate() and currhold.T_QUANTITY_C  <> 0 THEN 1
              when currhold.status in ('BUY','SELL') AND currhold.gsettle_date > dbo.uf_getGLDate() and currhold.T_QUANTITY_C  <> 0 THEN 3
else 2
end,
myPrincBalAL= Case when currhold.T_QUANTITY_C > 0 Then 'A' else 'L' end,
myAccruedAmtAL= Case when currhold.t_accr_intrst_c + currhold.t_int_div_due_c > 0 Then 'A' else 'L' end,
myUnearnedpremAL= Case when (currhold.T_QUANTITY_C - currhold.GBOOK_VALUE_C) < 0 Then 'A' Else 'L' end,
myUnearnedDiscAL = Case when (currhold.T_QUANTITY_C - currhold.GBOOK_VALUE_C) < 0 Then '' Else 'L' end,
myUnrealProfitAL= Case when currhold.Gunrl_gl_ytd_c > 0 Then 'A' Else 'L' end,
myUnrealLossAL = Case when currhold.Gunrl_gl_ytd_c > 0 Then '' Else 'L' end,
myCouponRate = case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM' 
                   then Convert(decimal(20,7),currhold.COUPON_RATE/100) 
                   else Convert(decimal(20,7),security.COUPON_RATE/100) end,
myFixRate = Case when [security].Float_Rate = 'Y' 
then Convert(decimal(20,7),[security].index_rate_add/100) 
else case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM' 
          then Convert(decimal(20,7),currhold.COUPON_RATE/100) 
          else Convert(decimal(20,7),security.COUPON_RATE/100) end
end,
myFixVarInd=Case when Security.float_rate='Y' then 'V' else 'F' end,
myIntIncomeAL = case when (currhold.t_accr_intrst_c + currhold.Gchange_amz_c) > 0 Then 'I' else '' end,
Convert(varchar(12),currhold.gTrade_Date,112) as RB_VALDATE,
Convert(varchar(12),currhold.maturity_date,112) as Maturity_date,
Convert(varchar(12),currhold.gTrade_Date,112) as RB_TRADEDATE,
Replace([security].sec_type,' ','') as Product,[security].guarantor as CIF_No,
myTradeType = Case when currhold.Portfolio = 'ABST' THEN 'TRADING' else 'SALE' end,
Open_Flag='Y',
Convert(varchar(12),currhold.GSETTLE_DATE,112) as GSETTLE_DATE,
currhold.t_quantity_c as Quantity,
INT_RATE= case when ltrim(rtrim(currhold.sec_group)) = 'SHORT TERM' 
then currhold.Coupon_Rate - (security.Index_Rate_Add/100) 
else security.Coupon_Rate - (security.Index_Rate_Add/100) end,
security.Index_Rate_Add,
Convert(varchar(12),security.Next_PMT_Date,112) as Next_PMT_Date,
currhold.status,
currhold.account,
SECURITY.FLOAT_FREQ,
'SOURCE' as Source_Code,
Convert(varchar(12),@System_Date,112) as  System_Date,
#transactemp.broker,
security.issuer,
Convert(decimal(20,8),currhold.gorig_price) as Orig_Price,
isnull(portfoli.MGR2_TITLE,'') as OBU,
isnull(#tmpISB.Issuer_ISB,'') as Issuer_ISB,
Security.Author,
isnull(Convert(varchar(12),#pricetemp.Max_Price_Date,112),Convert(varchar(12),@System_Date,112)) as Max_Price_Date,
Security.Symb_pool,
isnull(Convert(varchar(12),Currhold.Issue_Date,112),'') as Issue_Date,
isnull(Convert(varchar(12),Security.Last_pmt_date,112),Convert(varchar(12),@System_Date,112)) as Last_pmt_date,
Security.Accrual,
isnull(Convert(varchar(12),Security.Last_float_Date,112),'') as Last_float_date,
isnull(Convert(varchar(12),Security.Next_Float_Date,112),'') as Next_float_date,
isnull([security].guarantor,'') as guarantor,
isnull(Convert(varchar(12),Currhold.Maturity_date,112),'') as LAST_PRIN_PMT,
Security.Country,
isnull(currhold.ANT_HEDGE,'') as ANT_HEDGE,
isnull(Broker.User_desc1,'') as Broker_ISB,
isnull(#tmpISB.Guarantor_ISB,'') as Guarantor_ISB
from securtyp,chartacc,security,portfoli,#factortemp,#pricetemp,currhold,#tmp_transac,issuer,#transactemp,broker,#tmpISB
where  
#tmpISB.cusip=security.cusip and
#transactemp.broker=broker.broker and
security.cusip=#transactemp.cusip and
#transactemp.current_hold__001 = currhold.current_hold__001 and
#transactemp.cusip = currhold.cusip and
currhold.current_hold__001 *= #tmp_transac.current_hold__001 and
((abs(t_quantity_c)> 0.01 and #tmp_transac.current_hold__001 is null) or
#tmp_transac.current_hold__001 is not null) and
portfoli.account=currhold.Account and 
portfoli.portfolio=currhold.Portfolio and
securtyp.sec_type=currhold.sec_type and
security.cusip=currhold.cusip and
currhold.account=chartacc.account and
currhold.portfolio = chartacc.portfolio and
currhold.sec_group = chartacc.sec_group and
currhold.sec_type=chartacc.sec_type and
currhold.custodian = chartacc.custodian and
currhold.cusip*=#factortemp.cusip and
currhold.cusip*=#pricetemp.cusip  and
security.issuer = issuer.issuer and
CURRHOLD.PORTFOLIO not in (select portfolio from ut_portfolio_exception where proc_name='SKY') 
and abs(CURRHOLD.T_QUANTITY_C) > 0.01
and currhold.account not in ('SEC PUR','SEC SOLD','RABO SEC','GOSMORE')

How to check for SQL Server Database version in WIX database installer

I have a database installer WIX tool and i want to check if sql server 2008 r2 is installed in the system and if it is not installed i want to show a message to install. How do i do it using WIX tool.....

Get Start of Year based on Last Month

I run a stored procedure on the 1st of the month that takes all data from the previous month and puts it into another table. I do it as follows:

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

insert into ThisTable
select Things
from AnotherTable
where AppsEntryDate >= DATEADD(month, -1, @startOfCurrentMonth)
and AppsEntryDate < @startOfCurrentMonth

I now need to build another query that does this on a yearly basis. So, right now it's March. I need to enter all the data from Jan 1 2015 through Feb 28 2015 using this same structure. The trick is, on Jan 1 2016, I need it to capture all data from 2015. How would I code this?

Why TSQL convert a function's result in one way and a character string to other way?

I try this command in SQL Server 2005 to obtain a MD5 from '123':

select SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',  '123' )), 3, 32) 

and I get this result:

202cb962ac59075b964b07152d234b70

I want to convert to binary format,

select 
   convert(varbinary(16), SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',  '123')), 3, 32)) 

And I get this result:

0x32003000320063006200390036003200

Why does this code:

select convert(varbinary(16), '202cb962ac59075b964b07152d234b70')

result in a different value?

0x32303263623936326163353930373562