isnull with DataTime - 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

SQL Server 2005 Forums

Transact-SQL (2005)

isnull with DataTime

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-12-11 : 00:48:38

select isnull(GetDate(), 10) -> 2009-12-11 11:16:53.013
select isnull('Babu', 10) -> Babu
select isnull(10, 'Babu') -> 10
select isnull(10.5, 'Babu') - >10.5
select isnull('Babu', 10.5) -> Babu

This statement produce the error.

Explain in details.

select isnull(10, GetDate())

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 01:04:01

Syntax

ISNULL ( check_expression , replacement_value )

Arguments

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

Return Types

Returns the same type as check_expression.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

stepson
Aged Yak Warrior

545 Posts

Posted - 2009-12-11 : 01:13:09

use coalesce
search in BOL for coalesce

baburk
Posting Yak Master

108 Posts

Posted - 2009-12-11 : 01:27:50

quote:
Originally posted by stepson

use coalesce
search in BOL for coalesce

select COALESCE(null, 'Babu' , 10)
Error: Conversion failed when converting the varchar value 'Babu' to data type int.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 01:32:10

quote:
Originally posted by baburk

quote:
Originally posted by stepson

use coalesce
search in BOL for coalesce

select COALESCE(null, 'Babu' , 10)
Error: Conversion failed when converting the varchar value 'Babu' to data type int.

Use same Datatypes on COALESCE..

Check for exact Syntax...

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-11 : 01:33:17

You must use CONVERT function since an implicit conversion is not allowed.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 01:33:44

COALESCE ( expression [ ,...n ] )

Arguments

expression

An expression of any data type.

A placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 01:35:58

quote:
Originally posted by baburk

quote:
Originally posted by stepson

use coalesce
search in BOL for coalesce

select COALESCE(null, 'Babu' , 10)
Error: Conversion failed when converting the varchar value 'Babu' to data type int.

Did you read the replies posted?
Did you think babu and 10 are of the same datatype?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources