Handling a Null datetime column

can anybody tell me how to do a select query on a datetime field where if i have a null value in that column, i need to display a some character.

ISNULL is a lovely function useful for doing just that.

ISNULL(MyDateColumn, 'ITS NULL!')

returns ITS NULL if column MyDateColumn's value is NULL

|||

select donor_id,isnull(check_date,'No Value') from donors where check_date is null

If the column check_date consists the null value U will get the value No Value.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||I would say it depends what is the type of your field. If your filed is data time you shoudl convert it to string for output to not have problems with data type.Looks that ISNULL is trying to convert default for null value to the same type like tested value so you you would like to use syntax like this:ISNULL(DateField, 'Null date')It can not work because SQL will try to convert you "NUll Date" to be datetime and will fail. So you have to use different syntax:ISNULL(convert(varchar(20),DateField), 'Null date')and this should work but returned column will be varchar(20) not datetime.In case you need datetime column just left nulls inside or use first valid date :ISNULL(DateField, '00:00')it will replace all nulls by date '01/01/1900 00:00' Bu I think that keeping null is better.|||

ISNULL wont work with datetime a column if we r replacing with some characters. so in this case whatjpazgiermentioned is right.but in that too there is a flaw. what i got here is we need to check for each part of the datetime value for NULL.like dd/mm/yyyy, then hh:mm:ss,then am/pm.

so the query will be like

SELECT column1,Isnull(
(convert(varchar(20),columnDate,101) + ' ' + convert(varchar(20),columnDate,108) + ' ' + right(convert(varchar(20),columnDate),2)),'-') column2 from table1

im not sure if the above mentioned is the best solution possible.if anyone have any easy method other than this please reply.