Return Text Not Numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return Text Not Numbers

Author

Topic

dekizian
Starting Member

8 Posts

Posted - 2008-09-03 : 20:02:42

WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '11' and '30' THEN '1130'

Instead of returning '1130' I want '11-30' returned or other text

dekizian
Starting Member

8 Posts

Posted - 2008-09-03 : 20:09:43

Allow me to clarify, the expression above is only allowing me to return numbers such as '1130' - is there a way to return text?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-03 : 20:13:53

THEN '11-30'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dekizian
Starting Member

8 Posts

Posted - 2008-09-03 : 20:18:37

When '11-30' is entered the following error msg is returned:

Syntax error converting the varchar value '11-30' to a column of data type int.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-03 : 22:46:21

What is the data type of FA_NEXTDUE? Could you post the query instead of just a snippet?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 00:58:29

The error may be because you're trying to return an int value from some other conditions of CASE prior to this. The CASE WHEN construct requires that you return same data type values for all conditions. so what you could do is to convert the other consitions value to varchar. If you're not sure how to do this post your full case statement and we will help you out.

dekizian
Starting Member

8 Posts

Posted - 2008-09-04 : 09:58:53

The following is the full statement (Tara, FA_NEXTDUE refers to a column label of data I'm referencing in the table megaCON.dbo.FA_ccccACCT - thanks in advance for the help!)

SELECT CO as COMPANY
,FA_BRANCH as BRANCH
-- ,FA_CLASS as CLASS
-- ,FA_CLASSACCOUNT
,CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '10' THEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101))
WHEN DATEDIFF(day,FA_NEXTDUE,FA_LSTPAID) between '11' and '30' THEN '11-30'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '3160'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '61' and '90' THEN '6190'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '91' and '120' THEN '91120'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > '120' THEN '121' else 0 end as DPD
-- ,YEAR(ENTER_DATE) as YRBK
-- ,MONTH(ENTER_DATE) as MNBK
-- ,CASE WHEN FA_CLASS >= 800 then 'BOU' else 'LIQ' end as BOU
-- ,CASE WHEN fa_codate > 0 then DATEDIFF(month, ENTER_DATE, fa_codate) else 0 end AS 'Coff_Age'
-- ,SUM(fa_openbal - case when fa_intmeth = 'C1' then FA_OFINCHG else 0 end) as FINANCED
-- ,SUM(CASE WHEN FA_INTMETH = 'C1' then FA_ODISC else (CASE WHEN ISNUMERIC(FA_ACCTNOT) = 1
-- THEN CONVERT(money, FA_ACCTNOT) ELSE NULL END) END) as Discount
-- ,SUM(CASE WHEN fa_codate > 0 then fa_coamt else 0 end) as COAMT
,SUM(CASE WHEN fa_intmeth = 'C1' AND fa_curbal > 0 THEN fa_curbal - (fa_ueinc - fa_uedisc)
ELSE fa_curbal END * case when fa_codate >0 then 0 else 1 end) as CURBAL
-- ,COUNT(fa_account) as Units

FROM megaCON.dbo.FA_ccccACCT

WHERE fa_pstatus = 'a'

GROUP BY CO
,FA_BRANCH
-- ,FA_CLASS
-- ,FA_NEXTDUE
,CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '10' THEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101))
WHEN DATEDIFF(day,FA_NEXTDUE,FA_LSTPAID) between '11' and '30' THEN '11-30'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '3160'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '61' and '90' THEN '6190'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '91' and '120' THEN '91120'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > '120' THEN '121' else 0 end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:01:26

quote:
Originally posted by dekizian

The following is the full statement (Tara, FA_NEXTDUE refers to a column label of data I'm referencing in the table megaCON.dbo.FA_ccccACCT - thanks in advance for the help!)

SELECT CO as COMPANY
,FA_BRANCH as BRANCH
-- ,FA_CLASS as CLASS
-- ,FA_CLASSACCOUNT
,CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '10' THEN CAST(DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) AS varchar(10))
WHEN DATEDIFF(day,FA_NEXTDUE,FA_LSTPAID) between '11' and '30' THEN '11-30'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '3160'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '61' and '90' THEN '6190'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '91' and '120' THEN '91120'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > '120' THEN '121' else 0 end as DPD
-- ,YEAR(ENTER_DATE) as YRBK
-- ,MONTH(ENTER_DATE) as MNBK
-- ,CASE WHEN FA_CLASS >= 800 then 'BOU' else 'LIQ' end as BOU
-- ,CASE WHEN fa_codate > 0 then DATEDIFF(month, ENTER_DATE, fa_codate) else 0 end AS 'Coff_Age'
-- ,SUM(fa_openbal - case when fa_intmeth = 'C1' then FA_OFINCHG else 0 end) as FINANCED
-- ,SUM(CASE WHEN FA_INTMETH = 'C1' then FA_ODISC else (CASE WHEN ISNUMERIC(FA_ACCTNOT) = 1
-- THEN CONVERT(money, FA_ACCTNOT) ELSE NULL END) END) as Discount
-- ,SUM(CASE WHEN fa_codate > 0 then fa_coamt else 0 end) as COAMT
,SUM(CASE WHEN fa_intmeth = 'C1' AND fa_curbal > 0 THEN fa_curbal - (fa_ueinc - fa_uedisc)
ELSE fa_curbal END * case when fa_codate >0 then 0 else 1 end) as CURBAL
-- ,COUNT(fa_account) as Units

FROM megaCON.dbo.FA_ccccACCT

WHERE fa_pstatus = 'a'

GROUP BY CO
,FA_BRANCH
-- ,FA_CLASS
-- ,FA_NEXTDUE
,CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '10' THEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101))
WHEN DATEDIFF(day,FA_NEXTDUE,FA_LSTPAID) between '11' and '30' THEN '11-30'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '3160'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '61' and '90' THEN '6190'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '91' and '120' THEN '91120'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > '120' THEN '121' else 0 end

modify like above and try

dekizian
Starting Member

8 Posts

Posted - 2008-09-04 : 12:40:46

Visakh16 - thanks for the help

your suggestion produced the same result for the string you changed

CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '10' THEN CAST(DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) AS varchar(10))

but that result was numeric. The function is supposed to take the difference between the system date and the FA_NEXTDUE (next due date of the loan) and indicate how many days past due (delinquent) the loan is. Between 1 and 10 days the above function is supposed to display 1,2,3,4,5,6,7,8,9 or 10. I'm running into a problem on the next string

WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '3160'

when I want to combine all results between 31 and 60 days past due to display "31-60 Days" instead of just "3160"

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 12:56:44

Not sure what your're trying to achieve. can you show your sample output>?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-04 : 13:01:15

And one more:

quote:
Originally posted by visakh16

quote:
Originally posted by dekizian

The following is the full statement (Tara, FA_NEXTDUE refers to a column label of data I'm referencing in the table megaCON.dbo.FA_ccccACCT - thanks in advance for the help!)

SELECT CO as COMPANY
,FA_BRANCH as BRANCH
-- ,FA_CLASS as CLASS
-- ,FA_CLASSACCOUNT
,CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '10' THEN CAST(DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) AS varchar(10))
WHEN DATEDIFF(day,FA_NEXTDUE,FA_LSTPAID) between '11' and '30' THEN '11-30'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '3160'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '61' and '90' THEN '6190'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '91' and '120' THEN '91120'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > '120' THEN '121' else '0' end as DPD
-- ,YEAR(ENTER_DATE) as YRBK
-- ,MONTH(ENTER_DATE) as MNBK
-- ,CASE WHEN FA_CLASS >= 800 then 'BOU' else 'LIQ' end as BOU
-- ,CASE WHEN fa_codate > 0 then DATEDIFF(month, ENTER_DATE, fa_codate) else 0 end AS 'Coff_Age'
-- ,SUM(fa_openbal - case when fa_intmeth = 'C1' then FA_OFINCHG else 0 end) as FINANCED
-- ,SUM(CASE WHEN FA_INTMETH = 'C1' then FA_ODISC else (CASE WHEN ISNUMERIC(FA_ACCTNOT) = 1
-- THEN CONVERT(money, FA_ACCTNOT) ELSE NULL END) END) as Discount
-- ,SUM(CASE WHEN fa_codate > 0 then fa_coamt else 0 end) as COAMT
,SUM(CASE WHEN fa_intmeth = 'C1' AND fa_curbal > 0 THEN fa_curbal - (fa_ueinc - fa_uedisc)
ELSE fa_curbal END * case when fa_codate >0 then 0 else 1 end) as CURBAL
-- ,COUNT(fa_account) as Units

FROM megaCON.dbo.FA_ccccACCT

WHERE fa_pstatus = 'a'

GROUP BY CO
,FA_BRANCH
-- ,FA_CLASS
-- ,FA_NEXTDUE
,CASE WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '1' and '10' THEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101))
WHEN DATEDIFF(day,FA_NEXTDUE,FA_LSTPAID) between '11' and '30' THEN '11-30'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '31' and '60' THEN '3160'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '61' and '90' THEN '6190'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between '91' and '120' THEN '91120'
WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > '120' THEN '121' else 0 end

modify like above and try

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-04 : 13:09:00

Also, why are you comparing INTs to VARCHARs? Try somthing more like this:

CASE 
	WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between 1 and 10 
		THEN CAST(DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) AS varchar(10)) 
	WHEN DATEDIFF(day,FA_NEXTDUE,FA_LSTPAID) between 11 and 30 
		THEN '11-30' 
	WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between 31 and 60 
		THEN '3160'
	WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between 61 and 90 
		THEN '6190'
	WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) between 91 and 120 
		THEN '91120'
	WHEN DATEDIFF(day,FA_NEXTDUE,convert(varchar(10),getdate(),101)) > 120 
		THEN '121' 
	ELSE '0' 
END AS DPD

dekizian
Starting Member

8 Posts

Posted - 2008-09-04 : 13:52:53

Lamprey --

Problem solved! Thanks to you, visakh16 and Tara for the help!

Doug

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources