function - 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

function

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-19 : 04:02:22

how to write a funtion to show negative number as n/A

i have a computed coloumn in a table overtime

if the difference is negative i want to display text na other wise the no

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 04:04:13

No need for a function to write.
Show your SELECT statement please.

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

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-19 : 04:11:49

its a computed column

i wrote the formula like this, if it is negative i want to display NA
otherwise the difference

(datediff(minute,[FirstIn],[FirstOut])/(60.0)-datediff(minute,[EmpSIN],[EmpSOut])/(60.0))

Devart
Posting Yak Master

102 Posts

Posted - 2010-10-19 : 04:17:12

Hi,

For example:

case when 0>(datediff(minute,[FirstIn],[FirstOut])/(60.0)-datediff(minute,[EmpSIN],[EmpSOut])/(60.0))
then 'N/A'
else cast((datediff(minute,[FirstIn],[FirstOut])/(60.0)-datediff(minute,[EmpSIN],[EmpSOut])/(60.0)) as varchar(20)) end

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-19 : 04:22:54

is this function or i can write simply in my query

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 04:24:54

Note that the datatype of a computed column is defined implicitly by the calculation expression.
So your original computed column is of a numeric datatype.
If you want to assign a value like 'N/A' then there is type mismatch between numeric and character.

Hence Devart has converted the column to varchar.
Consider this when using this column...

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 04:26:01