Sql query with functions

Question

SELECT
         SLTR_COMP_CODE,
         sltr_ldgr_code,
         sltr_slmast_acno Acno,
         SUBSTRING(sltr_slmast_acno, 1, 1) category,
         ISNULL(SUM(CONVERT(FLOAT, sltr_tran_amt - ISNULL(sltr_matched_amt, 0))), 0) Net_Bal,
         SUM(CONVERT(FLOAT, CASE DBO.glas_agewise_analysis(CONVERT(DATETIME, '2009-04-04', 103) - FLOOR(sltr_pstng_datetime)) 
            WHEN 1 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0)) 
            ELSE 0 
        END)) thirty_days,
         SUM(CONVERT(FLOAT, CASE DBO.glas_agewise_analysis(CONVERT(DATETIME, '2009-04-04', 103) - FLOOR(sltr_pstng_datetime)) 
            WHEN 2 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0)) 
            ELSE 0 
        END)) sixty,
         SUM(CONVERT(FLOAT, CASE DBO.glas_agewise_analysis(CONVERT(DATETIME, '2009-04-04', 103) - FLOOR(sltr_pstng_datetime)) 
            WHEN 3 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0)) 
            ELSE 0 
        END)) ninty,
         SUM(CONVERT(FLOAT, CASE DBO.glas_agewise_analysis(CONVERT(DATETIME, '2009-04-04', 103) - FLOOR(sltr_pstng_datetime)) 
            WHEN 4 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0)) 
            ELSE 0 
        END)) One_twenty,
         SUM(CONVERT(FLOAT, CASE DBO.glas_agewise_analysis(CONVERT(DATETIME, '2009-04-04', 103) - FLOOR(sltr_pstng_datetime)) 
            WHEN 5 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0)) 
            ELSE 0 
        END)) One_fifty
FROM  glas_sl_transactions 
WHERE    SLTR_COMP_CODE  = '1'
 AND    sltr_ldgr_code  = '01'
GROUP BY sltr_ldgr_code,
     sltr_slmast_acno,
      SLTR_COMP_CODE

error: Msg 260, Level 16, State 3, Line 1 Disallowed implicit conversion from data type datetime to data type float, table 'glas_sl_transactions', column 'SLTR_PSTNG_DATETIME'. Use the CONVERT function to run this query.

how can i run this query?

given that you've been a member for 4 months, I would have thought you would know how to at least format the code? — Mitch Wheat
– Mitch Wheat, Commented Jan 6, 2010 at 8:52

David M · Accepted Answer · 2010-01-06 08:54:20Z

2

Try replacing your expressions with:

CONVERT(float, CONVERT(datetime, '2009-04-04', 103))
    - FLOOR(CONVERT(float, sltr_pstng_datetime))

so that all your conversions are explicit.

answered Jan 6, 2010 at 8:54

David M

73.2k13 gold badges164 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sql query with functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related