CODEWITHSUNDEEP
sql
maztt
maztt

Reputation: 12294

SQL query convert

This works absolutely fine

Update TradeFlow 
set flt_TotalPriceUSD= 
(Select top 1 (flt_ExRate * TradeFlow.flt_TotalPrice) 
from ExchangeRate ex inner join DateConversion dc 
on ex.dtm_Date = dc.gDate and  pDate=Convert(varchar,'01') + '/' 
+  Convert(varchar,'03') + '/' + Convert(varchar,'1389'))

but when I substitute it with values from the database (TradeFlow.int_Month and TradeFlow.int_Year) it stops working. pdate is a string 

Update TradeFlow 
set flt_TotalPriceUSD = (Select top 1 (flt_ExRate * TradeFlow.flt_TotalPrice)
from ExchangeRate ex inner join DateConversion dc   
on ex.dtm_Date = dc.gDate and  pDate=Convert(varchar,'01') + '/' +  
Convert(varchar,TradeFlow.int_Month) + '/' 
+ Convert(varchar,TradeFlow.int_Year))

any idea why ?

Upvotes: 1

Views: 127

Answers (2)

dani herrera
dani herrera

Reputation: 51675

My suggestion:

Instead of:

Convert(varchar,TradeFlow.int_Month) ...
Convert(varchar,TradeFlow.int_Year))

write:

right( '0' + rtrim(ltrim(str(TradeFlow.int_Month))), 2) ...
right( '0000' + rtrim(ltrim(str(TradeFlow.int_Year))), 4) ...

Upvotes: 2

Michał Powaga
Michał Powaga

Reputation: 23183

Update TradeFlow 
set flt_TotalPriceUSD= (Select top 1 (flt_ExRate * TradeFlow.flt_TotalPrice) 
    from ExchangeRate ex inner join DateConversion dc   
on ex.dtm_Date = dc.gDate and  
    --if pDate is varchar type (`dd/MM/yyyy`)
    pDate= '01/' + right('0' + cast(TradeFlow.int_Month as varchar), 2) + '/' + 
        cast(TradeFlow.int_Year as varchar)

    --if pDate is datetime type
    pDate=  cast(TradeFlow.int_Year as varchar) + 
        right('0' + cast(TradeFlow.int_Month as varchar), 2) + '01'

Upvotes: 2

Related Questions