Link to home
Start Free TrialLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

Need help with converting varchar data types to display numeric with commas for 1,000s!

I need help with converting a varchar field that contains numeric values that need to be formatted with commas if over 1,000.   Please note the following SQL and image of the error I get:  'Argument data type varchar is invalid for argument 1 of format function.'


No, doubt the errors in regards the field Volume and Total_Volume which are varchar.  How do I convert to numeric and display the #,###?


User generated image


Can someone please send me the proper SQL syntax that would display this information properly?


Here is the SQL in question:


SELECT 

Location, 

Batch_ID, 

Acct_ID, 

Product, Format([Volume],'#,###') AS Volume_, 

Format([Total_Volume],'#,###') AS Total_Volume_, 

Start_Time, 

Carrier_Consignee, 

Reference_Date AS ScheduleDate, 

Reference_Date, 

Time_Stamp, 

Schedule_Name

FROM C_SEmails

WHERE C_SEmails.Schedule_Name = 'E Imperial Force'

ORDER BY Reference_Date DESC, Time_Stamp DESC, Batch_ID


Thank you in advance.

Avatar of Lee W, MVP
Lee W, MVP
Flag of United States of America image

Have you tried casting Total_Volume as Numeric or another type of numeric value?


For example:

SELECT 

Location, 

Batch_ID, 

Acct_ID, 

Product, Format(Volume,'#,###') AS Volume_, 

Format(CAST(Total_Volume AS BIGINT),'#,###') AS Total_Volume_, 

Start_Time, 

Carrier_Consignee, 

Reference_Date AS ScheduleDate, 

Reference_Date, 

Time_Stamp, 

Schedule_Name

FROM C_SEmails

WHERE C_SEmails.Schedule_Name = 'E Imperial Force'

ORDER BY Reference_Date DESC, Time_Stamp DESC, Batch_ID


(I've not tested this, give it a shot.  And I used BigInt because I don't know what kind of values you have stored.  Also note, if the information in Total_Volume isn't numeric only (for example, the field has data like "2750 sqft" then it's probably going to fail.  But I assume it doesn't have that problem or you would have indicated that the data was like that.

Hi,
If the data type is varchar, you can convert it to be integer or decimal first like

select cast('9000' as int)
go

select cast('9000.02' as decimal(10,2))
go

before formating it to be the amount value.
SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dfke
dfke

Hi,

Something like:

SELECT 
    Location, 
    Batch_ID, 
    Acct_ID, 
    Product, 
    FORMAT(CAST(Volume AS NUMERIC), '#,###') AS Volume_, 
    FORMAT(CAST(Total_Volume AS NUMERIC), '#,###') AS Total_Volume_, 
    Start_Time, 
    Carrier_Consignee, 
    Schedule_Date AS ScheduleDate, 
    Reference_Date, 
    Time_Stamp, 
    Schedule_Name
FROM 
    C_SEmails
WHERE 
    Schedule_Name = 'E Imperial Force'
ORDER BY 
    Schedule_Date DESC, 
    Time_Stamp DESC, 
    Batch_ID

Open in new window



Cheers
Use TRY_CAST instead of plain CAST:

SELECT 
    Location, 
    Batch_ID, 
    Acct_ID, 
    Product, 
    FORMAT(TRY_CAST(Volume AS NUMERIC), '#,###') AS Volume_, 
    FORMAT(TRY_CAST(Total_Volume AS NUMERIC), '#,###') AS Total_Volume_, 
    Start_Time, 
    Carrier_Consignee, 
    Schedule_Date AS ScheduleDate, 
    Reference_Date, 
    Time_Stamp, 
    Schedule_Name
FROM 
    C_SEmails
WHERE 
    Schedule_Name = 'E Imperial Force'
ORDER BY 
    Schedule_Date DESC, 
    Time_Stamp DESC, 
    Batch_ID

Open in new window


TRY_CAST will give the same result as CAST IF the data can be converted
BUT
TRY_CAST will return NULL if the data cannot be converted, instead of CAST which will just stop your query with an error - and you won't know what caused the error.

If you want to check for conversion errors first, see my previous answer.