How to append a character to a DATEDIFF result? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to append a character to a DATEDIFF result?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-05-20 : 09:30:15

I have this query stub:


select 
	poitem.fpartno,
	pomast.forddate,
	poitem.frcpdate,
	CASE poitem.frcpdate
	WHEN '1900-01-01 00:00:00.000' THEN
		DATEDIFF(d, pomast.forddate, GETDATE())
	ELSE
		DATEDIFF(d, pomast.forddate, poitem.frcpdate)
	END AS DAYS
from 
	poitem left join pomast on poitem.fpono = pomast.fpono
	and LTRIM(RTRIM(pomast.fstatus)) not in ('CANCELLED', 'AWAITING APPROVAL', 'ON HOLD')
	and poitem.fnextrels < 1
	
where 
	LTRIM(RTRIM(poitem.fpartno)) in ('widget01')
order by 
	pomast.forddate

Which yields

Widget01 2010-01-13 2010-01-15 2
Widget01 2010-04-01 2010-04-16 15
Widget01 2010-05-07 1900-01-01 13

I need to append a '+' to the result of the DATEDIFF if the value in poitem.frcpdate is the default field value '1900-01-01 00:00:00.000', to indicate that the amount of time passed between order and receipt is still growing.

Apparently it's not as simple as appending + '+'; is there a way to do this? I was hoping to avoid an extra field flagging received/not received, but if I have to I will go that route.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-20 : 09:49:56

In both CASEs convert the output of datediff to varchar then you can use your + '+'.
convert(varchar(10),DATEDIFF(....))+'+'

No, you're never too old to Yak'n'Roll if you're too young to die.

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-05-20 : 10:13:16

quote:
Originally posted by webfred

In both CASEs convert the output of datediff to varchar then you can use your + '+'.
convert(varchar(10),DATEDIFF(....))+'+'

No, you're never too old to Yak'n'Roll if you're too young to die.

Gah, so simple a caveman could do it. Which explains why it mystified me. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources